Ignore SqlBulkCopy errors

  • Thread starter Thread starter fingermark
  • Start date Start date
F

fingermark

If it's possible, can someone tell me how I could ignore and log
SqlBulkCopy duplicate key errors?

So, if the bulkCopy.WriteToServer(dt); errors on duplicate key, I want
it to log what the row items that caused the error and then to skip
over this row... inserting the next.
 
no. bcp works by inserting sets. you could set the batch size to 1, but then
you might as well do a insert. you can insert into a work table and then use
a set operation to add the new rows. this is the most efficient way.


-- bruce (sqlwork.com)
 
thanks, bruce. so if my bulk size is 10 and the 12th item in the
datatable that i'm copying is a duplicate in the target table, then
those 10 would remain inserted after the exception unless i set this up
in a transaction, right?

also, what is a work table?

last question: now i want to prevent any records from the datatable to
be inserted (i would have to rollback, right?), how could i alert the
user what row caused the error. right now it just says the table name
affected by the duplicate.
 
bulk copy works in batches. each batch is a complete transaction, so the
batch either committed or not. if you want to rollup back the complete
insert, you need to use the bulk insert statement nested in a transaction.

a work table is a table with the same column layout as the load table but no
indexes. you truncate then bulk into the work table. then you a sql insert
to move the work table rows to the destination table. you can use a ranking
function to de-dup.

-- bruce (sqlwork.com)
 
Back
Top