how can I ignore duplicate inserts?

Question

I basically have the same question posed here - Optimal way to ignore duplicate inserts? - except in my case it's SQL Server.

I have a table that is a list of person IDs who are deceased. There are many places in the source database that a person can be indicated as deceased. I would like to do INSERTS from each of these locations into this main table, but a person may be indicated as deceased in multiple places.

Ideally (I think) I just want to ignore duplicate key errors - in the most efficient way possible. (Thus I don't want to have to check the main table for the IDs as part of my INSERT statement.)

(related to solving my question need suggestions to improve view performance)

JHFB · Accepted Answer · 2012-12-18T13:14:58.937

4

The solution to this lies in the table creation itself. If I set IGNORE_DUP_KEY = ON on the index on my person ID column, the duplicate values are simply ignored and the rest are inserted appropriately.

edited Dec 18 '12 at 13:14

answered Dec 18 '12 at 12:36

JHFB

2,874
6
39
64

score 2 · Answer 2 · answered Jan 23 '15 at 12:14

You can also use the EXCEPT clause in your insert statement. It will most likely be faster than the IGNORE_DUP_KEYS option. See here for details and a benchmark:

http://www.sqlservercentral.com/Forums/Topic856838-392-1.aspx

A third option is to use the MERGE command.

A fourth option is to use the where not in trick

score 1 · Answer 3 · answered Jan 23 '15 at 12:27

I have used a merge statement to do this in the following format:

merge into [dbo].[table]
using [dbo].[Stage_Table] on Stage_Table.pk = table.pk
when not matched then insert (val1) values (1234);

Re-reading your question, this probably wouldn't have efficiency you are looking for, but may be useful information for other readers.

how can I ignore duplicate inserts?

3 Answers3