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

User · Accepted Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   --  gt  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 lt  gt  0 AND  Any error lt  gt 2627 GOTO ErrorHandler      INSERT INTO Table1 Value1  VALUES  Value1       SELECT  Any error     ERROR     IF  Any error lt  gt  0 AND  Any error lt  gt 2627 GOTO ErrorHandler      INSERT INTO Table1 Value1  VALUES  Value2       SELECT  Any error     ERROR     IF  Any error lt  gt  0 AND  Any error lt  gt 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server