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