Continue Batch Load of SQL Database Table with Primary Key violaions with Duplicate Reject File

Question

How do I successfully load an SQL Database Table with Primary Key to prevent duplicates; but still continue the batch load of only unique records after duplicates are found and send rejects/duplicates to a separate table or flat file?

Answer 1

If you want put the rejections elsewhere, you will have no choice put to roll your own. That is, insert the data into a staging table, which has one extra column:

row_number() OVER(PARTITION BY pkcols ORDER BY somecriteria) AS keyno

Those with keyno = 1 you insert in to your target table, and those with keyno >= 2 you send to your log table.

If you can accept to drop the rejects on the floor, you can define the primary key with the option (IGNORE_DUP_KEYS = ON). You will only get a warning a duplicate is rejected, but the statement will not abort.

If you want something better than this, you can vote on this old feedback item:
https://feedback.azure.com/forums/908035-sql-server/suggestions/32910640-new-virtual-table-errors-it-would-analogous-to
(It says that there are only four votes, but that is for the current site. On the old Connect site, this item garnered 650 votes.)