Fix 'Cannot insert duplicate key' in SQL Server fast

Yeah, error 2627 is a pain. You're just trying to add a record, and SQL Server throws that duplicate key violation in your face. Let's get you past it.

The quick fix: find and remove the duplicate

Most of the time, the duplicate data already exists in your table. Here's how to find and kill it.

1. First, note the exact table and column from the error message. It'll say something like Cannot insert duplicate key in object 'dbo.Users'. The duplicate key value is (john.doe@example.com).
2. Run this query to find the duplicate row:

SELECT column_name, COUNT(*) as count
FROM dbo.YourTable
GROUP BY column_name
HAVING COUNT(*) > 1;

Replace column_name with the actual column from the error. If it's a composite key (multiple columns), list them all in the GROUP BY.

3. Once you see the duplicates, decide which ones to keep. Usually you want to keep the most recent or the one with the highest ID. Delete the rest:

DELETE FROM dbo.YourTable
WHERE PrimaryKeyID IN (
    SELECT MIN(PrimaryKeyID)
    FROM dbo.YourTable
    GROUP BY column_name
    HAVING COUNT(*) > 1
);

This keeps the oldest row and deletes the newer duplicates. Flip MIN to MAX to keep the newest.

4. Now try your insert or update again. It should go through without error.

Why this works

Error 2627 fires because SQL Server enforces uniqueness on primary keys, unique constraints, and unique indexes. Your insert or update is trying to create a value that already exists in that column or combination of columns. By deleting the duplicate row, you're making room for the new data — assuming the new data is actually what you want. If the existing row is the correct one, you should update it instead of inserting a new one.

Less common variations of the same issue

1. The insert is perfectly fine, but a trigger is causing the violation

Sometimes the error points to a table you're not directly inserting into. That's a trigger firing. To check:

1. Run EXEC sp_help 'YourTableName'; and look for triggers in the results.
2. Disable the trigger temporarily to test:

DISABLE TRIGGER TriggerName ON YourTableName;
-- run your insert again
ENABLE TRIGGER TriggerName ON YourTableName;

If the insert works with the trigger off, the trigger is the source. You'll need to fix the logic inside it.

2. The duplicate exists in a view or indexed view

If you're inserting into a table that's part of an indexed view with a unique clustered index, the same rule applies — no duplicates allowed. Check for indexed views with SELECT * FROM sys.indexed_views WHERE object_id = OBJECT_ID('YourViewName');. Then remove the duplicate from the base table and rebuild the view index.

3. The duplicate comes from a different session that committed just before yours

This is a race condition. Two users try to insert the same unique value at the same time. The fix is to add a retry loop in your application code. Here's a T-SQL example:

DECLARE @retry INT = 0;
WHILE @retry < 3
BEGIN
    BEGIN TRY
        -- your insert statement here
        BREAK;
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() = 2627 AND @retry < 2
        BEGIN
            WAITFOR DELAY '00:00:01';
            SET @retry = @retry + 1;
        END
        ELSE
            THROW;
    END CATCH
END

This retries up to 3 times with a 1-second pause between attempts. It doesn't fix the root cause, but it handles the race condition gracefully.

How to prevent this from happening again

The real fix is to check for existence before inserting. Here's the pattern I use:

IF NOT EXISTS (SELECT 1 FROM dbo.YourTable WHERE column_name = @value)
BEGIN
    INSERT INTO dbo.YourTable (column_name, other_columns)
    VALUES (@value, @other_values);
END
ELSE
BEGIN
    -- either update the existing row or log the conflict
    PRINT 'Duplicate found. Skipping insert.';
END

Putting a unique constraint on the column is good — it catches problems at the database level. But combining it with an existence check in your application gives you the best of both worlds: no errors and clean data.

Also, if you're bulk loading data, always use WITH (IGNORE_DUP_KEY = ON) on your unique indexes. That way, when a duplicate hits, SQL Server just skips it instead of throwing an error and stopping the whole batch.

One last thing: if this happens a lot, it's a sign your data entry process has a design flaw. Look at where the data is coming from — is it user input, an ETL job, a third-party API? Each source needs its own deduplication logic before it hits the database.