i've just been playing with the same thing. CHeck out linked servers.
The following code will create a linked server called "csvtest" to a directory c:\temp on your SQL server, using the text OLEDB driver, set to comma delimited and a header row existing:
EXEC master.dbo.sp_addlinkedser
so assuming your csv file is called "myfile.csv" and you place it in c:\temp on the SQL server,
to query from it run this:
select * from csvtest...myfile#csv
note the # takes place of the period in the filename (so this is actually myfile.csv)
This behaves just like a normal table. So you can join to it, or create a temp table and do an INSERT INTO to bring that data to a real temp table and then do your joins to that.. etc. Whatever you like.
for my purposes, however, the CSV file resides on another server - not the SQL server. This is where I had to get tricky. When the lnked server is using the EXCEL driver, you can specify UNC paths and providing the SQL Service logon account has permissions to the UNC path it works. But no matter what I tried, I couldn't get this working when using a text file (if anyone has managed to get it working then please let me know!!). So, I used FTP instead:
EXEC master.dbo.sp_addlinkedser
so same thing as above but this time the data source instead of being c:\temp is an FTP path. the select statement remains the same.
hope this helps
cheers
Dave
Main Topics
Browse All Topics
by: momi_sabagPosted on 2008-03-28 at 22:42:27ID: 21235664
so you want to update existing records and add new ones ?
if so, you should use an ssis package
if you just insert new rows, you can use bulk insert