Question: I’m investigating a persistent error message Error 2627
Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'. The duplicate key value is %ls.
How can I troubleshoot and fix?
Answer: In summary , the unique index on the column is preventing the statement from commiting.
Another record exists with the same value on the unique index column.
Error 2627 is closely related to a constraint violation. Constraints are similar to Unique Indexes – when a UNIQUE CONSTRAINT is created a UNIQUE INDEX is automatically created. Read more on Difference between UNIQUE CONSTRAINT versus UNIQUE INDEX
Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'. The duplicate key value is %ls.
Data is validated in the same way and both check for unique data
The first step is to identify the sql statements causing the Exception. SQL Server Profiler can display the Exceptions.
Once you’ve identified the sql statements – work is required to understand why the application is attempting to insert duplicate values into the UNIQUE INDEX.
It is easy to identify the record already in the table. Use the value from the statement throwing the exception. Run a SELECT statement on the table , to return the row already in the table.
Some methods to avoid the error include:
a) Use the IF NOT EXISTS logic
b) Use the NOT EXISTS logic