Transact SQL – Error Message – SQLState = 23000, Native Error = 2627 – Violation of Primary Key Constraint

Background

Trying to bulk-load a huge amount of data into MS SQL Server.

But, unfortunately the table already contains a few records with matching keys.

What to do?

So what to do.

Googled till I was sweaty.

Finally, the most useful advice is the one documented in SQLMag.com:

Umachandar Jayachandran – A Bulk-Copy Procedure
http://www.sqlmag.com/article/sql-server/a-bulk-copy-procedure

The solution is actually attributed to

  • Alejandro Mesa, a database programmer for Simplex Medical in Fort Myers, Florida
  • Marcos Kirchner, a student at the University of Blumenau (FURB) in Santa Catarina, Brazil

The advice is to create a unique index on the columns that infer sameness and be sure to indicate that duplicates should simply be discarded.

 

SQL

The following statement creates the new index:

Syntax

CREATE UNIQUE INDEX [index-name]
ON [table-name]
(
     [column-1]
   , [column-2]
   , [column-3]
)
WITH ignore_dup_key

Sample

CREATE UNIQUE INDEX [uq_idx_companies_id]
ON Companies(CompanyId)
WITH ignore_dup_key

Interpretation

So it looks like it is OK to leave everything as is, just create a unique index (with ignore_no_dup) that has the same columns as the primary key. Import the data and go back and drop the ignore_dup_key index.

One thought on “Transact SQL – Error Message – SQLState = 23000, Native Error = 2627 – Violation of Primary Key Constraint

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s