How to Ignore "Duplicate Key" error in T-SQL (SQL Server)

Question

I have a transaction that contains multiple SQL Statements (INSERT, UPDATE and/or DELETES). When executing, I want to ignore Duplicate Error statements and continue onto the next statement. What's the best way of doing that?

Thanks in Advance.

Answer 1

Keys must be unique. Don't do that. Redesign as needed.

(if you are trying to insert, then delete, and the insert fails... just do the delete first. Rollback on error in either statement).

Answer 2

If by "Ignore Duplicate Error statments", to abort the current statement and continue to the next statement without aborting the trnsaction then just put BEGIN TRY.. END TRY around each statement:

BEGIN TRY
    INSERT ...
END TRY
BEGIN CATCH /*required, but you dont have to do anything */ END CATCH
...

Answer 3

I think you are looking for the IGNORE_DUP_KEY option on your index. Have a look at http://sqlkit.com/2009/06/17/ for an explanation.

Answer 4

Expanding on your comment to SquareCog's reply, you could do:

INSERT INTO X VALUES(Y,Z)    WHERE Y  NOT IN (SELECT Y FROM X)
INSERT INTO X2 VALUES(Y2,Z2) WHERE Y2 NOT IN (SELECT Y FROM X2)
INSERT INTO X3 VALUES(Y3,Z3) WHERE Y3 NOT IN (SELECT Y FROM X3)

Here, I assume that column Y is present in all three tables. Note that performance will be poor if the tables are not indexed on Y.

Oh yeah, Y has a unique constraint on it--so they're indexed, and this should perform optimally.

Answer 5

Although my emphatic advice to you is to structure your sql so as to not attempt duplicate inserts (Philip Kelley's snippet is probably what you need), I want to mention that an error on a statement doesn't necessarily cause a rollback.

Unless XACT_ABORT is ON, a transaction will not automatically rollback if an error is encountered unless it's severe enough to kill the connection. XACT_ABORT defaults to OFF.

For example, the following sql successfully inserts three values into the table:

create table x ( y int not null primary key )

begin transaction
insert into x(y)
values(1)
insert into x(y)
values(2)
insert into x(y)
values(2)
insert into x(y)
values(3)
commit

Unless you're setting XACT_ABORT, an error is being raised on the client and causing the rollback. If for some horrible reason you can't avoid inserting duplicates, you ought to be able to trap the error on the client and ignore it.

Answer 6

OK. After trying out some error handling, I figured out how to solve the issue I was having.

Here's an example of how to make this work (let me know if there's something I'm missing) :

SET XACT_ABORT OFF ; -- > really important to set that to OFF
BEGIN
DECLARE @Any_error int
DECLARE @SSQL varchar(4000)
BEGIN TRANSACTION
    INSERT INTO Table1(Value1) VALUES('Value1')
    SELECT @Any_error = @@ERROR
    IF @Any_error<> 0 AND @Any_error<>2627 GOTO ErrorHandler

    INSERT INTO Table1(Value1) VALUES('Value1')
    SELECT @Any_error = @@ERROR
    IF @Any_error<> 0 AND @Any_error<>2627 GOTO ErrorHandler

    INSERT INTO Table1(Value1) VALUES('Value2')
    SELECT @Any_error = @@ERROR
    IF @Any_error<> 0 AND @Any_error<>2627 GOTO ErrorHandler

    ErrorHandler: 
       IF @Any_error = 0 OR @Any_error=2627
       BEGIN 
           PRINT @ssql 
           COMMIT TRAN
       END
       ELSE 
       BEGIN 
           PRINT @ssql 
           ROLLBACK TRAN 
       END
END

As a result of the above Transaction, Table1 will have the following values Value1, Value2.

2627 is the error code for Duplicate Key by the way.

Thank you all for the prompt reply and helpful suggestions.