May 11, 2010 at 11:14 am
I have a situation where my director has provided me with a spreadsheet of our user base...The spreadsheet contains the user's mnemonics and a month and year of their birthday...
He needs me to import that birthday information into a SQL table that is part of our intranet solution....So I will need to look at the UN from the spreadsheet, match that UN in the SQL table and then update the birthday information...then rinse,repeat...
I've looked at the wizard based import solution and if I was creating a new table or simply adding the excel data as new information in the table that would certainly seem to do the trick...My problem is I'm not sure how to tweak it to do what I've detailed out above...
OR if there is a better solution what that might be...
Hoping someone can offer some guidance that I can use to get working on this request...
Thanks in advance for any help that is offered...
May 11, 2010 at 12:35 pm
so you have a spreadsheet that has some column that need to be updated in the database table. and one of the columns in the spreadsheet provides a unique match to the identifier in the database correct? How many rows are you doing, and is this a one off request or are you trying to set up a process to do this on a regular basis?
Andrew
May 11, 2010 at 12:36 pm
Dont know how can you solve that issue using SQL but SSIS would be the best and easiest way.. If you want to try, Let me know i can help you with the Steps/process
May 11, 2010 at 12:46 pm
Andrew...that's correct...Sorry forgot to include that I'll be updating two pieces of info/data the users birth month and birth day as two separate fields....I'd love to say this is a one time deal but there is a very distinct possibility that I may have to re-hash it over time...However I don't see at this point in time it being one of those deals that needs to run every Friday night for instance...
SSC....to me it's all a learning opportunity so I'm more than willing to go that route if you wish to lay out some steps to follow...
Sorry, off topic but I have to ask when you say SSC Enthusiastic what does SSC refer to?
May 11, 2010 at 12:58 pm
well, SSIS is a great way to setup a process that needs to be run repeatedly, but is definitely more work and a bit overkill for this. But like you said, it's maybe a great learning opportunity.
If i were busy and just needed to get this done quickly, i would build a formula in Excel that creates an update statement to update each record and drag the formula down, so you have a unique update statement for each row.
Example
column A | column B | column C | column D
1235 | 12 | 1982 | ="update tblEmployee set month=" & B1 & " and year = "& C1 & " where employeeID = " & A1
once you drag the formula in column D down, you will have a unique update statement for each Excel row. Execute one first to make sure you have it correct, then copy and paste all the statements into SSMS and execute at once. Assuming you aren't doing hundreds of thousands of rows, it shouldn't take too long.
May 11, 2010 at 1:02 pm
and SSC = SQL Server Central...it's kinda his level based on his input.
May 11, 2010 at 1:06 pm
Thanks Andrew!! I'll work with your suggestion...definitely never even occurred to me to go that route which is what is so great about places like this...Sometimes a good educated set of eyes will show you what tends to be right in front of your face 🙂
Okay...makes sense...I used to be involved with a hobby where the acronym SSC had signifigance which is why I had asked...Again SQL Server Central never even occurred to me...
May 11, 2010 at 2:39 pm
Worked like a charm...simple, quick and effective...Thanks again for your help!!!
May 11, 2010 at 3:26 pm
Great! Glad I could be of some help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply