Delete does not work in transaction with bulk sql insert

Question

I need to delete some records from a table, and then insert some records into the same table. This deletion and insertion process must be performed in a transaction.

Below I did it.

using (SqlConnection sqlConn = new SqlConnection(connectionString)) { sqlConn.Open(); using (SqlTransaction sqlTran = sqlConn.BeginTransaction()) { string deleteQuery = "delete from dbo.MyTable where Col1 =" + colValue; SqlCommand sqlComm = new SqlCommand(deleteQuery, sqlConn,sqlTran); sqlComm.ExecuteNonQuery(); using (SqlBulkCopy sqlcopy = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.Default, sqlTran)) { sqlcopy.BatchSize = 10; sqlcopy.DestinationTableName = "MyTable"; try { sqlcopy.WriteToServer(dsDataSet.Tables[0]); sqlTran.Commit(); } catch (Exception ex) { sqlTran.Rollback(); } } } } 

But, I think, since the delete operation is not performed before the insert, I get duplicate key errors. Can anyone help.?

Answer 1

I tried your sample code and it seems to work for me. I created a PK on one of the columns to make sure that the duplicate insert would cause an error. Then I run the code twice - the first time some fictitious data is placed through it (without errors), the second time I try to delete the data through it, and then again insert the same transaction. From your question, I was hoping to get an exception a second time, but it worked. Not sure if this is important, but I am running SQL Server 2008 R2 SP1.

Here is the complete test code I used, maybe this will help you identify your problem.

First, SQL will create a sample table:

 CREATE TABLE [dbo].[MyTable]( [Col1] [nvarchar](20) NOT NULL, [Col2] [nvarchar](20) NULL, [Col3] [nvarchar](30) NULL, CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED ( [Col1] ASC ) ) 

And C #:

 public static void Main() { DataTable t = new DataTable(); t.Columns.Add(new DataColumn("Col1")); t.Columns.Add(new DataColumn("Col2")); t.Columns.Add(new DataColumn("Col3")); for (int i = 0; i < 5; i++) { var r1 = t.NewRow(); r1["Col1"] = "1" + i.ToString(); r1["Col2"] = "2" + i.ToString(); r1["Col3"] = "3" + i.ToString(); t.Rows.Add(r1); } t.AcceptChanges(); var connectionString = new SqlConnectionStringBuilder(); connectionString.DataSource = "localhost"; connectionString.InitialCatalog = "testdb"; connectionString.IntegratedSecurity = true; using (SqlConnection sqlConn = new SqlConnection(connectionString.ToString())) { sqlConn.Open(); using (SqlTransaction sqlTran = sqlConn.BeginTransaction()) { string deleteQuery = "delete from MyTable"; // just delete them all SqlCommand sqlComm = new SqlCommand(deleteQuery, sqlConn, sqlTran); sqlComm.ExecuteNonQuery(); using (SqlBulkCopy sqlcopy = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.Default, sqlTran)) { sqlcopy.BatchSize = 10; sqlcopy.DestinationTableName = "MyTable"; try { sqlcopy.WriteToServer(t); sqlTran.Commit(); } catch (Exception ex) { sqlTran.Rollback(); } } } } } 

Update on your question By the way, you did not get an error, because 11/23/2011 was evaluated as a mathematical expression ('/' is a division), resulting in a value of 0, which was then implicitly passed to datetime as 1900/01 / 01. Try running the query "select CONVERT (datetime, 11/23/2011)" and you will see what I mean.