Ignore SqlBulkCopy errors

6 Sep 2006 7:26 PM

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.

6 Sep 2006 8:22 PM

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)

<fingerm***@gmail.com> wrote in message
Show quoteHide quote

news:1157570815.777448.221210@p79g2000cwp.googlegroups.com...
> 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.
>

6 Sep 2006 8:32 PM

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.

bruce barker (sqlwork.com) wrote:
Show quoteHide quote

> 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)
>
> <fingerm***@gmail.com> wrote in message
> news:1157570815.777448.221210@p79g2000cwp.googlegroups.com...
> > 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.
> >

7 Sep 2006 12:56 AM

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)

<fingerm***@gmail.com> wrote in message
Show quoteHide quote

news:1157574743.955316.274170@p79g2000cwp.googlegroups.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.
>
> bruce barker (sqlwork.com) wrote:
>> 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)
>>
>> <fingerm***@gmail.com> wrote in message
>> news:1157570815.777448.221210@p79g2000cwp.googlegroups.com...
>> > 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.
>> >
>