SYSK 60: How Does SqlBulkCopy Deal with Duplicate Records?

The other day, I was asked the following question “Does the SqlBulkCopy update records if they already exist or must it be used only for INSERTS only?”  So, I thought others might benefit from knowing the answer…

SqlBulkCopy is nothing more than a more efficient INSERT…  That means that if there is an identical row in the destination table, it will be repeated.  That is, if it’s possible given the table constrains.

Q:  What happens if you have an identity primary key that’s the same as a primary key of a record being inserted via SqlBulkCopy? 
A:  The primary key will be auto-incremented, and you’ll see a duplicate row with only difference being in the primary key value.

Q:  What happens if you have a non-identity primary key that’s the same as a primary key of a record being inserted via SqlBulkCopy? 
A:  The SqlBulkCopy method will throw an exception “Violation of PRIMARY KEY constraint 'Your_PK_Constrain_Name_Here'. Cannot insert duplicate key in object 'dbo.Test'.   The statement has been terminated.”

Now, since I believe in teaching to fish rather than giving a fish, here is how you could’ve figured this out:

1. Create a simple table (e.g. table Test with pk_id (identity), SomeValue, LastUpdatedTimestamp columns)
2. Add a row (e.g. Insert Into Test(SomeValue, LastUpdatedTimestamp) Values (123, getdate())
3. Create a simple .NET windows app with the following code under button click:
       SqlConnection cn = new SqlConnection("integrated security=SSPI;data source=(local);initial catalog=Test");
SqlDataAdapter da = new SqlDataAdapter("select top 1 * from Test", cn);
DataSet ds = new DataSet();
da.Fill(ds, "Test");

       System.Data.SqlClient.SqlBulkCopy bc = new SqlBulkCopy(cn);
bc.DestinationTableName= "Test"; // copy to self
4. Run it
5. In Sql Management Studio, run the following query:  
       select * from Test
   You should now see two rows (pk_id 1 and 2)
6. Now change pk_id to not be an identity field
7. Run the C# program again – you should get an exception

Now you know!