The internetz is a world wide library, but I actually couldn’t find one article that would spell out this solution. So…
You have a linked server, or a .csv file, or an excel sheet. You have a SQL server. You want to push data from the file to the server.
Fear not! There is a statement for that.
Say there is your table ‘herd’ and you just received data on 50000 million more goats. You want to load that information into ‘herd’ to make sure everything is at the same place. Right.
INSERT INTO herd(animals)
SELECT (Goats)
from openrowset('Microsoft.ACE.OLEDB.12.0','EXCEL 12.0;Database=S:\temp\List_of_my_Goats.xlsx;HDR=YES;IMEX=1','select * from [Sheet1$]')
Take it to a spin. SQL server will open List_of_my_Goats.xlsx, look for ‘Goats’ column on Sheet 1, and insert the values into the table’s animals column. Goats = Animals.
Now say you actually have a whole Library of Congress worth of data on your new Goats. Sex, color, age, ability_to_bite, hostility_to_goat_tippers, and the like.
You want to load *some* of these data into your table, but only a defined set of columns.
INSERT INTO herd(animals, age, sex)
SELECT (Goats) as [animals],
age as [age],
sex as [sex]
from openrowset('Microsoft.ACE.OLEDB.12.0','EXCEL 12.0;Database=S:\temp\List_of_my_Goats.xlsx;HDR=YES;IMEX=1','select * from [Sheet1$]')
SQL server will open List_of_my_Goats.xlsx, look for the columns on Sheet 1, and insert the values into the table’s columns. Goats = Animals. Age = age, sex = sex.
Now, just an FYI – I ran into some syntax errors when the excel sheet’s columns were named in a multi-part manner (goats.sex or goats.age). If you receive the usual ‘multi part identifier can not be bound’ error, just go ahead and rename the columns.
Conclusion: The world is a happy place with Openrowset.
Recent Comments