SQL DB Transaction fails when executing INSERT after DELETE, on duplicated PK

Question

_{Thursday, May 3, 2012 5:00 AM}

Hi,

Your help is very much appreciated.

When running a DELETE and then INSERT (BULK INSERT) sql commands , I am receiving an error

“Cannot insert duplicate key”.

It seems like the DELETE doesn’t actually delete the records, and therefore the INSERT fails.

[Framework 4.0, SQL Server 2008].

Heres' the code I'm using:

public static bool RunInTransInternal(List<SqlCommand> cmds, string conn)

        {

            try

            {

                using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew,

                    new TransactionOptions()

                    {

                        IsolationLevel = System.Transactions.IsolationLevel.Serializable,

                        Timeout = TimeSpan.FromHours(2)

                    }))

                {

                    using (SqlConnection connection1 = new SqlConnection(conn))

                    {

                        connection1.Open();

                        for (i = 0; i < cmds.Count; i++)

                        {

                            cmds[i].Connection = connection1;

                            cmds[i].CommandTimeout = 36000;

                            cmds[i].ExecuteNonQuery();

                        }

                    }

                    scope.Complete();

                }

            }

            catch (Exception ex)

            {

                return false

            }

            return true;

        }

Cheers

<city w:st="on"><place w:st="on">Roy</place></city>

All replies (6)

_{Friday, May 4, 2012 4:47 AM ✅Answered | 1 vote}

Insert is not possible because of if you don't delete the previous row with primary key you are inserting then insert won't never succeed. You need to do some other logic to first delete row in one transaction, then data is in consistent state and then insert new row with the primary key you just deleted.

Just for example you might create separate table where you copy rows you are about to delete. Perform copy and delete in transaction so if inserting to copy table fails or deleting from source table fails then you can rollback that transaction and no harm is done. If this goes ok, data is copied and deleted, you then known you have the original rows and you can perform insert of the new rows.

_{Sunday, May 6, 2012 10:30 PM ✅Answered}

Fully agree with you MasaSam.

I can see two options here:

1. Using a temp table as you suggested

2. UPSERT in SQL SERVER 2008
   http://blog.dezfowler.com/2011/05/bulk-upsert-to-sql-server-from-net.html
   http://www.databasejournal.com/features/mssql/article.php/3739131/UPSERT-Functionality-in-SQL-Server-2008.htm
   http://blogs.msdn.com/b/cellfish/archive/2008/04/29/upsert-in-sql-server-2008.aspx

_{Thursday, May 3, 2012 5:24 AM}

So you first run delete on row and then try to insert that same row back with same primary key at the transaction? You need to commit the delete transaction before you can insert new row with the same key. The actual delete is not happened considering the consistency of the database until you have committed the transaction that performs the delete.

_{Thursday, May 3, 2012 9:11 PM}

Thanks,

if the DELETE and INSERT run separately (not within the same transaction), the INSERT will succeed.

However, I want to maintain data integrity, hence running both in one transaction

(i.e. if the INSERT fails, I don't want to DELETE to execute..)

_{Thursday, May 3, 2012 9:16 PM}

On 5/3/2012 5:11 PM, win programmer wrote:

> Thanks,

>

> if the DELETE and INSERT run separately (not within the same

> transaction), the INSERT will succeed.

>

> However, I want to maintain data integrity, hence running both in one

> transaction

>

> (i.e. if the INSERT fails, I don't want to DELETE to execute..)

Well, you need to come up with some different logic, because this is not

going to work for you.

_{Thursday, May 3, 2012 9:17 PM}

You can use some boolean flag to do it so:

bool bContinue = false;
//do insert:
//...
try
{
   cmd.ExecuteNonQuery();
   bContinue = true;
}
catch (Execption ex)
{
    //show exception...
}

//now, if insert was ok, then continue with delete query:
if(bContinue)
{
    //do delete...
}

Mitja