Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question
    
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

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
3  
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
1  
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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