Ignore SqlBulkCopy errors

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.
 
B

bruce barker \(sqlwork.com\)

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)
 
F

fingermark

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.
 
B

bruce barker \(sqlwork.com\)

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,480
Members
44,900
Latest member
Nell636132

Latest Threads

Top