Bulk Insert with error Violation of PRIMARY KEY

In MSSQL 2008, when doing a bulk insert, it sometimes gets into the error of "Violation of PRIMARY KEY".

It should report the violation key value: "The duplicate key value is (...)". Then one can remove the duplicate and insert again.

However, sometimes this is not reported, due to unknown reason; or sometimes one just wants to continue the insert without interruption.

This can be achieved by adding the "WITH (IGNORE_DUP_KEY = ON)" option when creating the table, at the cost of a much more complex execution plan.

An alternative is to add a temporary unique key on the same column as the primary key, adding the "WITH (IGNORE_DUP_KEY = ON)" option on this temporary key and do the insert. After insert is done, remove the temporary key.

See A creative use of IGNORE_DUP_KEY for the details, and more discussions.

NOTE: for bulk insert, if it fails (like due to violation of PK constraint), then none record is inserted at all.

Bulk insert syntax:

BULK INSERT Test1
FROM 'C:\\temp\test1.txt'
WITH
(
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n'
)

Add a unique index with IGNORE_DUP_KEY on:

ALTER TABLE dbo.Test1
ADD CONSTRAINT UQ_idk
UNIQUE NONCLUSTERED (objID)
WITH (IGNORE_DUP_KEY = ON, ONLINE = ON);

Remove the index:

ALTER TABLE dbo.Test1
DROP CONSTRAINT UQ_idk