CODEWITHSUNDEEP
sql-servercsv
lisovaccaro
lisovaccaro

Reputation: 33996

SQL Server Bulk Insert Update from CSV?

I'm importing a CSV file to SQL server. The problem is that I need to update if rows are found, but I haven't found an INSERT UPDATE equivalent or something similar to do it.

This is my current code:

BULK INSERT Actuals_Financials_Temp FROM '\\STRMV3302\Temp\Actuals_Financials_Temp.csv'  
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 2)

Is there a way to update the rows which matching keys? Or at least ignore them so the rest is uploaded and then do something like a BULK UPDATE?

Upvotes: 1

Views: 6888

Answers (1)

Solomon Rutzky
Solomon Rutzky

Reputation: 48914

Not directly, no. You need to BULK INSERT into a staging table and then UPDATE the existing records and INSERT the missing records. I would try with a local temp table (i.e. #TableName) first.

Technically speaking, you could probably do either of the following (both of which use OPENROWSET):

  • Skip the staging table and use OPENROWSET(BULK...) for the UPDATE and then INSERT queries. This would have to be tested, though, to see if the cost of reading the file twice is worth the savings of not having to read it into a temp table (which just writes it back to disk). It is possible that using the staging table might still be better since the first query, the UPDATE, might auto-create some statistics that would benefit the second query, the INSERT, since that query will need either a LEFT JOIN or a WHERE NOT EXISTS.
  • MERGE along with OPENROWSET(BULK...), but MERGE has some "issues" so I wouldn't try this.

Upvotes: 3

Related Questions