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

[royadams]

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
Roy

[BioPhysEngr]

A little out of my expertise here, but perhaps you need to execute two transactions? One to commit the deletes and another to commit the updates?

Kind of just a guess though. Someone know better?

[royadams]

Yes, 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..)

[BioPhysEngr]

Never hurts to ask! :-)

Other stupid question: where is cmds assigned? Is every delete at a lower index in the list than every insert?

Second idea: you can use the INSERT ... ON DUPLICATE KEY ... syntax; see e.g.: https://dev.mysql.com/doc/refman/5.0...duplicate.html

[forgottenhart]

Why not just set conditions tests?

private void ExecuteQuery()
{
	if(DeleteRecord)
	{
		InsertRecord();
	}
	else
	{
		MessageBox.Shoe("Can Not Complete");
		
	}

}

public bool DeleteRecord()
{
	try
	{
		//Deleting Record
		return true;
	}
	catch
	{
		return false;
	}
}

private void DeleteRecords()
{
	//DeleteRecords here
}

private void InsertRecords()
{
	//Insert the Records Here
}

[BioPhysEngr]

Good thought, forgotten, but if the insert should fail, the delete should also fail. Thus, there must be some way of bundling them together in the same transaction...

[royadams]

Thanks guys,

I can see two options here:


1. Using a temp table (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)

2. UPSERT in SQL SERVER 2008
http://blog.dezfowler.com/2011/05/bu...-from-net.html
http://www.databasejournal.com/featu...erver-2008.htm
http://blogs.msdn.com/b/cellfish/arc...rver-2008.aspx