0

I am using SqlBulkCopy to insert batches of records to a server. If one record fails because of a unique constraint, I don't want to rollback the whole batch, but I want to do an update of the existing record instead.

I was hoping I could log the failing records key values then once the bulk insert is finished, go back and address them individually.

However I can't see any way of telling SqlBulkCopy to not to rollback the transaction on failure, nor how to get info on the failing record.

I could check the record exists before adding it to the SqlBulkCopy DataTable, but that would add considerable overhead.

I have checked the answers here which indicate using a staging table and some other sproc to do the insert/update after the bulk insert, but I think that sproc would need to process each record individually to either insert new or update the existing records - very time-consuming.

Any other tips or has nothing changed in the last 4 years?

Community
  • 1
  • 1
Graeme
  • 2,597
  • 8
  • 37
  • 50

2 Answers2

1

Do not use SqlBulkCopy.

Directly.

Insert into a tempoary table (also avoids the horrific locking behavior of SqlBulkCopy) then MERGE into the final table with the proper rules.

Simple, done, this is how I do all my bulk updates.

TomTom
  • 61,059
  • 10
  • 88
  • 148
  • Didn't know about MERGE so will have to do some homework there. Can you expand on the horrific locking behaviour? – Graeme Jun 17 '14 at 16:17
  • SqlBulkCopy tries to get a table exclusive lock. THat is not an issue - but it tries with no waiting period. If you run multiple concurrently it will fail all the time on most. After trying repeatedly for 30 seconds it gives up. The issue is it never waits, so it always fails on a busy table that has some locks. If it would wait for 30 seconds it would actually work better. We du mass bulk inserts - lots of the msame time (many computers doing calcualtion) and that totally killed everything performance wise. – TomTom Jun 17 '14 at 16:19
  • Shouldn't really be an issue for me as it's a read-only table except for the bulk uploads. – Graeme Jun 17 '14 at 16:22
  • Still, due to that behavioe I wrote my own wrapper - which also accepts objects and not a data table. a lot easier. Depending on call i do insert from staging or merge (by primary key). – TomTom Jun 17 '14 at 16:22
  • Thanks for the pointers to MERGE and the issues with SqlBulkCopy. For my purposes I will use the latter then use the SQL suggested by Steve. – Graeme Jun 20 '14 at 11:29
1

I wouldn't recommend using the merge statement. It has issues see Use Caution with SQL Server's MERGE Statement first of many, and personally IMHO I find it difficult to follow and offers little if anything over a simple insert update statement (not so sweet syntactic sugar).

INSERT INTO A
SELECT *
FROM   #B B
       LEFT OUTER JOIN A
               ON A.ID  = B.ID
WHERE  A.ID IS NULL

or

UPDATE A
SET    BLAH = 'BLAH'    
FROM   A
       INNER JOIN #B B
               ON B.ID  = A.ID

If you are set on a tool rather than pure sql you may want to look into SSIS which can handle what you are looking for.

Steve
  • 710
  • 1
  • 6
  • 12