CODEWITHSUNDEEP
c#sql-serversqlbulkcopy
bumble_bee_tuna
bumble_bee_tuna

Reputation: 3563

SQLBulkCopy Don't Copy Primary Keys

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();

Upvotes: 4

Views: 3733

Answers (2)

Andomar
Andomar

Reputation: 238086

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

Upvotes: 1

zmbq
zmbq

Reputation: 39013

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.

Upvotes: 6

Related Questions