CODEWITHSUNDEEP
sql-server-2008csvssis
salvationishere
salvationishere

Reputation: 3511

Updating a SQL table with CSV data?

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.

Upvotes: 2

Views: 7518

Answers (2)

Zahra
Zahra

Reputation: 7207

This is probably too late to solve salvationishere's problem; though I'm posting this for future readers! You could just generate the SQL INSERT/UPDATE/etc command by parsing the csv file (a simple python script will do).

You could alternatively use this online parser: http://www.convertcsv.com/csv-to-sql.htm (Hoping that it'd still be available when you click!) to generate your SQL command. The interface is extremely straight forward and it does the entire job in an awesome way.

Upvotes: 4

Registered User
Registered User

Reputation: 8395

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.

Upvotes: 2

Related Questions