Eric M Russell (3/30/2015)

---

Most ETL jobs from source to target want to perform both inserts for new rows and updates for existing rows having the same primary key. However, if you only need to insert new rows (rows having a new primary key), and you are not concerned with updating existing rows, then this fairly straightforward.

One option is to set IGNORE_DUP_KEY = ON on the table's primary key, which means that any incoming rows with an existing primary key are simply ignored, meaning they are not inserted. Once that is in place, you can just slam the records home using BCP utility or INSERT.. SELECT..

This would probably be the most optimal in terms of performance, but carefully consider the implications of this. I'll admit to being a reductionist and by default I will implement the simplist solution unless or until the underlying assumptions change, because I've found that approach to be most optimal in terms of performance usually.

A creative use of IGNORE_DUP_KEY

http://sqlblog.com/blogs/paul_white/archive/2013/02/01/a-creative-use-of-ignore-dup-key.aspx