tsql : best way to update a db table with a csv file

Question

I have a products table in sql server 2005 which needs to be updated by certain fields in a csv file. Both files have a vendor part number which can be linked to where I can update the products.discontined field with another in the csv file.

My question is what is the best way to approach this?

I've considered creating a odbc connection to the excel file and figuring out how to merge update the 2 columns. Import the entire csv file (~60 MB) into a temp table in sql server and then write a tsql procedure to search,compare, update? Also doing and opensource command from query analyzer and writing a procedure to read in the csv file and update the table that way..

thanks in advance

Answer 1

If it's available to you, your best bet with SQL Server 2005 is the SQL Server Integration Services (formerly Data Transformation Services or DTS). This will let you create a package that can reside anywhere and be run on a schedule or invoked whenever you decide. It can run asynchronously to the application calling it as well.

update:
additionally you can run and debug the package to get it perfect before you decide to publish it. This will allow you to account for things such as malformed documents etc.

if you do not have SSIS available to you then you could do a bulk insert (reference:http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/)

create a table that matches the outline of your csv then import it using the following:

BULK
INSERT MyTable
FROM ‘c:\mycsv.csv’
WITH
(
    FIELDTERMINATOR = ‘,’,
    ROWTERMINATOR = ‘\n’
)

from there you can transform it using tsql as it is in a table in your db.

if it is a constant update you can create temp tables to do your work and run the stored procedure as a job.