none
SqlBulkCopy redirecting error rows RRS feed

  • Question

  • Is there a way to redirect error rows when using a bulkcopy WriteToServer using C#?

    I have two tables, one is the real table and the other is an error table (all the columns are varchar). I am using SqlBulkCopy to extract rows from Oracle and then insert into MS Sql Server, some rows have errors in them, so I want to redirect the error rows into the error table.

    How would I accomplish this when using SqlBulkCopy?

    I have been trying a try catch block, but when the catch is raised, it seems like i cant get the error rows or continue with the extraction of the rest of the rows. It also seems that if its in a transaction, its all or nothing. But, this is not acceptable for me as I need the error rows.

    You are so wise...like a miniature budha covered in fur. -Anchorman
    Friday, October 29, 2010 6:57 AM

Answers

  • Hi Reza,

    The error handling of the SqlBulkCopy is a bit restricted (see also here), which is wy I would use the bulkcopy to insert into a temp table. I would then use a separata SP to transfer the records to the target table. By applying a diff to the target and temp you can move the surplus to the error table.  (I have done this in the past and it worked like a charm).

    Kind regards,

    Tom de Koning


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by liurong luo Thursday, November 4, 2010 9:24 AM
    Saturday, October 30, 2010 11:17 AM