SQLBulkCopy does not copy primary keys

Question

What is the best way to deal with 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 will it need to be checked and processed before hand?

Here is the code I'm 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(); 

Answer 1

I usually run a bulk copy operation on a temporary table and then copy the data from it to the target table using plain SQL. This allows me to perform “bulk updates” as well as deal with such special situations (although I have not come across this particular need).

There is performance compared to direct volume copy, but it is still much faster than performing INSERT.

Answer 2

You can customize the original query to eliminate duplicates. For instance:

 select distinct * from dbo.email 

Or to filter the first col1 for pkcol :

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