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?

Answer 1

I think you are looking for the IGNORE_DUP_KEY option on your index. Have a look at IGNORE_DUP_KEY ON option documented at http://msdn.microsoft.com/en-us/library/ms186869.aspx which causes duplicate insertion attempts to produce a warning instead of an error.

Answer 2

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 3

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 4

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 5

I'd like to chime in with the following:

If 99% of your data is going to insert without error doing a select beforehand results in a huge performance drop (like, in my case, from 200 lines/sec to 20 lines/sec) compared to "dumb" inserts and catching the occasional error.

After ignoring the "Violation of PRIMARY KEY constraint" errors things went back to being bottlenecked by other resources (headroom being defined as "what the bottlenecking resources don't have").

Which is the whole reason I landed on this discussion in the first place.

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.

Answer 7

Use IGNORE_DUP_KEY = OFF during primary key definition to ignore the duplicates while insert. for example

create table X( col1.....)

CONSTRAINT [pk_X] PRIMARY KEY CLUSTERED 
(
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY]

Answer 8

I came here because I was trying to do the same thing; I knew I had dupes in the source data but only wanted to update the target data and not add the dupes.

I think a MERGE works great here because you can UPDATE or DELETE things that are different and INSERT things that are missing.

I ended up doing this and it worked great. I use SSIS to loop through Excel files and load them into a "RAW" SQL table with dupes and all. Then I run a MERGE to merge the "raw" table with the production table. Then I TRUNCATE the "raw" table and move to the next Excel file.

Answer 9

For SQL server 2000:

     INSERT INTO t1 (ID, NAME)
      SELECT valueid, valuename
      WHERE  NOT EXISTS
               (SELECT 0
                FROM   t1 as t2
                WHERE  t2.ID = valueid AND t2.name = valuename)

Answer 10

INSERT INTO KeyedTable(KeyField, Otherfield)
SELECT n.* FROM 
    (SELECT 'PossibleDupeLiteral' AS KeyField, 'OtherfieldValue' AS Otherfield
     UNION ALL
     SELECT 'PossibleDupeLiteral', 'OtherfieldValue2'
    )
LEFT JOIN KeyedTable k
    ON k.KeyField=n.KeyField
WHERE k.KeyField IS NULL

This tells the Server to look for the same data (hopefully the same speedy way it does to check for duplicate keys) and insert nothing if it finds it.

I like the IGNORE_DUP_KEY solution too, but then anyone who relies on errors to catch problems will be mystified when the server silently ignores their dupe-key errors.

The reason I choose this over Philip Kelley's solution is that you can provide several rows of data and only have the missing ones actually get in:

Answer 11

Well you could solve this with a temp table..

DECLARE @RoleToAdds TABLE
([RoleID] int, [PageID] int)

INSERT INTO @RoleToAdds ([RoleID], [PageID])
    VALUES
    (1, 2),
    (1, 3),
    (1, 4),
    (2, 5)

INSERT INTO [dbo].[RolePages] ([RoleID], [PageID])
    SELECT rta.[RoleID], rta.[PageID] FROM @RoleToAdds rta WHERE NOT EXISTS 
        (SELECT * FROM [RolePages] rp WHERE rp.PageID = rta.PageID AND rp.RoleID = rta.RoleID)

This might not work for big amounts of data but for a few rows it should work!

Answer 12

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