Updating a SQL table with CSV data?

Question

I am trying to update one of my SQL tables with new columns in my source CSV file. The CSV records in this file are already in this SQL table, but this SQL table is lacking some of the new columns from this CSV file.

I already added the new columns to my SQL table structure via ALTER TABLE. But now I just need to import the data from this CSV file into the new columns. How can I do this? I am trying to use SSIS and SQL Server to accomplish this, but am pretty new to Excel.

Answer 1

You have several options:

1. If you are loading the data into a non-production system where you can edit the target tables, you could load the data into a new table, rename the old table to obsolete, and rename the new table to the old table name.

2. You can load the data into a staging table and then write a SQL statement to update the target table from the staging table.

3. You can open the CSV file in Excel and write a formula to generate an update script, drag the formula down across all rows so that you get a separate update statement for each row, and then run the separate update statements in management studio.

4. You can truncate the target table and update your existing ssis package that imports the file to use the new columns if you have the full history in your CSV file.

There are more options, but any of the above would probably be more than adequate solutions.