SQL Server Bulk Insert Update from CSV?

Question

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?

Insert into temp table from the CSV, update where exists, insert if not exists? could be messy though. — Alec.
– Alec., Commented Jun 25, 2015 at 14:44
Bulk inserts are fast because they don't typically write detail to the transaction log. A bulk load into an empty table can be minimally logged. Merging data, however, is likely to have quite a lot of action going on in the transaction log, for the updates to existing records, indexes, etc. An efficient workflow would be to bulk load the data into an empty table, add any indexes required to match to existing data, and then perform the inserts/updates required to the existing data. — Ed B
– Ed B, Commented Jun 25, 2015 at 15:45

Solomon Rutzky · Accepted Answer · 2015-06-25 14:52:41Z

3

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.

edited Jun 25, 2015 at 14:52

answered Jun 25, 2015 at 14:47

Solomon Rutzky

49.3k11 gold badges141 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server Bulk Insert Update from CSV?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related