Fix "Cannot insert duplicate key row in object" SQL Error

Quick answer for advanced users: Run SELECT * FROM YourTable WHERE PrimaryKeyColumn = 'OffendingValue'; then delete that row with DELETE FROM YourTable WHERE ... before retrying your insert.

Why this error happens

I know this error is infuriating, especially when you're sure your data is clean. It usually shows up when you're inserting a row with a primary key, unique constraint, or unique index value that already exists in the table. I've seen it most often in SQL Server 2019 and 2022 when bulk loading data from CSV files or merging from staging tables. The error gives you the index name and the duplicate value, which is a huge clue.

Step-by-step fix

1. Read the full error message. Copy the index name and the duplicate key value. For example: Cannot insert duplicate key row in object 'dbo.Users' with unique index 'IX_Users_Email'. The duplicate key value is (jdoe@example.com).
2. Find the conflicting row. Run a query to see what's already there:
   

   SELECT * FROM dbo.Users
   WHERE Email = 'jdoe@example.com';

   
   This returns the existing row. You may also check other columns if the index covers more than one column.
3. Decide how to handle it. Three options:
   
   • Delete the duplicate. If the existing row is stale or a test record, remove it:
     

     DELETE FROM dbo.Users WHERE UserID = 123;

     
     Be careful: if the row has foreign key references, you'll need to delete those first.
   • Update the existing row. If you want to keep the existing record but update its data:
     

     UPDATE dbo.Users
     SET Email = 'newemail@example.com'
     WHERE UserID = 123;

     
     Then the new insert with the old email will succeed.
   • Skip the insert. If your business logic allows it, use INSERT ... WHERE NOT EXISTS or a MERGE statement to ignore duplicates.
4. Retry your insert. After removing or updating the conflict, run your original insert command again. It should work now.

Alternative fixes if the main one fails

If you can't delete the row (maybe it's referenced elsewhere), try these:

• Use MERGE (upsert). This lets you insert if no match, update if match exists. Example:
  

  MERGE dbo.Users AS target
  USING (VALUES ('jdoe@example.com', 'John', 'Doe')) AS source (Email, FirstName, LastName)
  ON target.Email = source.Email
  WHEN MATCHED THEN
    UPDATE SET FirstName = source.FirstName, LastName = source.LastName
  WHEN NOT MATCHED THEN
    INSERT (Email, FirstName, LastName) VALUES (source.Email, source.FirstName, source.LastName);

  
  This is my go-to for ETL jobs because it's atomic and handles both cases in one statement.
• Disable the index temporarily — only in dev! Never in production. Use ALTER INDEX IX_Users_Email ON dbo.Users DISABLE; then re-enable after the insert. This can cause data integrity issues, so don't do it on live systems.
• Check for SET IDENTITY_INSERT ON. If the table has an identity column, you might be trying to insert a specific value that already exists. Verify identity values with DBCC CHECKIDENT('dbo.Users', NORESEED);.

Prevention tip

Stop this error before it happens. Add a unique constraint check in your application code or stored procedure. I always wrap inserts in a TRY...CATCH block and check for error number 2627. Then I either skip or update the row. Also, before bulk-loading data, use a staging table to identify duplicates and resolve them. A simple SELECT Email, COUNT(*) FROM dbo.StagingUsers GROUP BY Email HAVING COUNT(*) > 1; can catch batch-level duplicates. Finally, always back up your database before making changes to primary key data. You'll thank me later.