Re: Ignore SqlBulkCopy errors

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)


<fingermark@xxxxxxxxx> wrote in message
news:1157574743.955316.274170@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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)

<fingermark@xxxxxxxxx> wrote in message
news:1157570815.777448.221210@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.




.

Relevant Pages

  • Re: Inserting Multiple Rows
    ... In addition to that [BCP, BULK INSERT, DTS], it might be worth mentioning below two things. ... Group several INSERT in the same transaction. ... Each batch requires a network roundtrip, ... mean what we see as "GO" in Query Analyzer and the method you use in ADO.NET to send the command to SQL Server ...
    (microsoft.public.sqlserver.server)
  • Re: Inserting Multiple Rows
    ... In addition to that [BCP, BULK INSERT, DTS], it might be worth mentioning below two things. ... Group several INSERT in the same transaction. ... Each batch requires a network roundtrip, ... mean what we see as "GO" in Query Analyzer and the method you use in ADO.NET to send the command to SQL Server ...
    (microsoft.public.sqlserver.programming)
  • Re: Inserting Multiple Rows
    ... In addition to that [BCP, BULK INSERT, DTS], it might be worth mentioning below two things. ... Group several INSERT in the same transaction. ... Each batch requires a network roundtrip, ... mean what we see as "GO" in Query Analyzer and the method you use in ADO.NET to send the command to SQL Server ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: sql server,Please help!
    ... If you are just doing a one-off bulk load, ... in a single batch and one transaction will speed things up, ... Don't do each INSERT as a separate transaction. ... to see what SQL traffic is submitted to SQL Server. ...
    (microsoft.public.sqlserver.server)
  • Execution Plan Hinting
    ... BEGIN TRANSACTION ... The first is a deletion of the data and the second is the bulk insert ... Will this give sufficient hint to SQL about the order it processes it ... I've seen that some hints can be passed to SQL for optimizing, ...
    (comp.databases.ms-sqlserver)
Loading