SQLBulkCopy Don't Copy Primary Keys

c# sqlbulkcopy sql-server

Question

What is the best way to deal with the primary key violation errors when using SQLBulkCopy

Violation of PRIMARY KEY constraint 'email_k__'. Cannot insert duplicate key in object  'lntmuser.email'.

(i.e. if the row already exists in the destination table) ?

Is there a way to skip inserting duplicate rows or would this have to be checked and dealt with before hand ?

Here is the code I am currently using:

  var conPro = tx_ProConStr.Text;
  var conArc = tx_ArcConStr.Text;

  var con = new SqlConnection {ConnectionString = conPro};
  var cmd = new SqlCommand("SELECT * FROM dbo.email", con);
  con.Open();

  var rdr = cmd.ExecuteReader(); 
  var sbc = new SqlBulkCopy(conArc) {DestinationTableName = "dbo.email"};

  sbc.WriteToServer(rdr);

  sbc.Close();
  rdr.Close();
  con.Close();

Accepted Answer

I usually end up performing a Bulk Copy operation to a temporary table, and then copy data from it to the target table using regular SQL. This allows me to perform 'bulk updates', as well as take care of special situations like this (although I haven't encountered this specific need).

There's a performance hit compared to straight bulk copy, but it's still a lot faster than performing INSERTs.


Popular Answer

You could adjust the source query to exclude duplicates. For example:

select distinct * from dbo.email

Or to filter for the first col1 per pkcol:

select  *
from    (
        select  row_number() over (parition by pkcol order by col1) as rn
        from    dbo.email
        ) as SubQueryAlias
where   rn = 1



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why