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)

  • Is this a one-time operation? If not, will you use a stored procedure or anything else?
    – dezso
    Dec 18 '12 at 12:11
  • Ongoing operation - and can be part of a stored procedure.
    – JHFB
    Dec 18 '12 at 12:13
  • Without the check, how do you want to ignore duplicates? Either way yo go, somewhere you have to check.
    – dezso
    Dec 18 '12 at 12:20
  • I didn't think this through thoroughly - I can catch this in the stored procedure. I shall answer my own question shortly.
    – JHFB
    Dec 18 '12 at 12:23

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.

  • 4
    Isn't IGNORE_DUP_KEY a property of an index rather than a property of a column?
    – Lamak
    Dec 18 '12 at 13:11
  • excellent point - modified accordingly
    – JHFB
    Dec 18 '12 at 13:15
  • 2
    I hope the source data is clean enough that you know the duplicates will always contain the same version of the truth... This can be a really dangerous way to solve the problem (in general).
    – Jon Seigel
    Dec 18 '12 at 18:10
  • Agreed, but in this case I am simply gathering Person IDs from three separate queries and inserting them into a single column table... and my logic is if they are deceased in any of three queries, they are deceased.
    – JHFB
    Dec 18 '12 at 18:30

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:


A third option is to use the MERGE command.

A fourth option is to use the where not in trick


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.