Update SQL with excel files?
-
Hi,
i need to update 1500 employee records,
my new data i have in Excel,
What is the best way for doing this?Tnx
- stino - Friday, March 24, 2017 4:23 PM
If it's a one-off, the simplest way is to using the import wizard to load the spreadsheet into a table and then write an update between that table and the employee table to update the employee table.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
lookup ETL ssis + upsert
-
Or download the "ACE" drivers and use those to import the spreadsheet to a work table if it needs to be done more than once.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Or just create a formula in Excel similar to
=CONCATENATE("INSERT INTO table (fields) VALUES ('",A2and so on and so forth
Drag the formula down, then copy the column into SSMS or notepad as a .sql file for sqlcmd to execute.
Beware - somewhere in the 5,000 to 50,000 INSERT range SSMS quits working on the entire batch, so GO statements every few thousand are required.
-
Hi,
i am trying it now for a few days,
for inserting new records i have no problems but when i wanna merge excels to the database i get no result.I have the same problem when i wanna try to merge 2 xls files i get no result
the 2 xls contains the same columnnames
name and lastname
Xls1
Name,lastname
Peter, vdb
Stijn,dv
Xls2
Name,lastname
Peter, VandenBroek
Stijn,devolderNow i wanna change the lastname
in both sources in 'Input and Output properties 'set the IsSorted on True and
and in 'Output columns' i set in both sources the Column name SortkeyPosition 1
Then i add for xls1 a sort button and set to left join
then i add the merge join and connect the sort button to it and xls2
i set it on inner join,
and select name and lastname but when i press start, it says package completed with succes but when i go to the xls2 there is nothing changed?I must be doing something wrong but what?
-
What are you using to join your two tables? There appears to be no ID field in your data, so how do you know Peter? Vanderbroek is Peter vdb?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
What did you apply the output of the Merge to ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
The name is the id, there are just only 2 records in each xls file just to try it and every name is unique
-
- stino - Tuesday, April 4, 2017 11:12 AM
If you were expecting this set of SSIS objects to actually merge the two excel spreadsheets into one, you just don't understand what the merge transformation does. You'd have to map the output from the Merge Join to an Excel destination.. This is not the best way to do such a thing, and given the small data involved, I don't see value in doing such a thing. Is there a larger objectvve.?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
I need to merge on regular basis data from xls files to a SQL server, because i tried it before with SSIS (merge data from xls to SQLtable) and that didn't worked either i just wanna try it with excel.
- stino - Tuesday, April 4, 2017 11:40 AM
It can work fine with SQL Server, but you have to know how it works. In this case, perhaps just to try it out for yourself, you could set up an OLE DB destination that is tied to the output from the Merge Join, and create a table to be the subject of the OLE DB destination. Then run it once and see if you at least get the desired result set.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - stino - Friday, March 24, 2017 4:23 PM
Ok... let's start over. I can show you a fairly easy method using only T-SQL to do this but I need your help. Can you attach a sample spreadsheet that mimics your data (don't use real personal data) and some readily consumable data for your table. Please see the first link under "Helpful Links" in my signature line below for how to do that.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - stino - Tuesday, April 4, 2017 11:09 AM
You say every name is unique, but (and I don't want to scupper your master plan), but they aren't. I spent my entire teenage years being confused with someone else with the same name and birthdate, my dentist used to have to check my teeth and then work out which one I was from the records.. Names are not a unique identifier, and if you end up with two employees of the same name, that's going to cause you problems.
Your end file has 1,500 employees in it. I, personally, would say it has a pretty decent chance of having two people with the same name, or will if you get any new employees.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply