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

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

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