How to Ignore "Duplicate Key" error in T-SQL (SQL Server)

sql

sql-server

duplicate-key-error

try-catch

byNikita Barsukov·Dec 4, 2024

Avoid 'Duplicate Key' errors in SQL Server by using a MERGE statement that only inserts new rows and ignores duplicates. Here's how it looks:

MERGE YourTable AS Target
USING (VALUES ('NewData')) AS Source (Col)
ON Target.KeyCol = Source.Col
WHEN NOT MATCHED THEN INSERT (Col) VALUES (Source.Col);

Replace YourTable, KeyCol, and Col with your table, column names, and 'NewData' with the value to insert. This method dodges duplicate insert attempts — so no more error messages.

Step-by-step guide: Avoiding that Duplicate Key error

1. Silent index modification

With the IGNORE_DUP_KEY = ON option in your index, duplicated key insert attempts fall silent and won't disrupt your much-loved peace:

CREATE UNIQUE INDEX idx_your_column ON YourTable(YourColumn) WITH (IGNORE_DUP_KEY = ON); 
-- SQL Server: Now with ninja-like duplicity handling! 🐱‍👤

2. Error capturing with TRY/CATCH

By wrapping your insert statements within a TRY/CATCH block, you catch those pesky duplicate key exceptions and decide their fate. Rollback, anyone?

BEGIN TRY
    INSERT INTO YourTable (YourColumn) VALUES ('SomeValue');
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = 2627
    -- Here's the culprit! Duplicate key error or is it just Déjà vu?
    END;
END CATCH

3. Proactive record checks

Keep things tidy with a WHERE NOT EXISTS clause. You can check whether a record exists before even attempting an insertion:

INSERT INTO YourTable (Col)
SELECT 'NewData'
WHERE NOT EXISTS (SELECT 1 FROM YourTable WHERE Col = 'NewData');
-- Because 'existence is pain' for duplicates. - Meeseeks, probably

DIY Strategies for Duplicate Key Handling

1. Taming transactions

By toggling XACT_ABORT OFF, you avoid an auto-rollback on your duplicate key errors:

SET XACT_ABORT OFF;
BEGIN TRANSACTION;
-- SQL insert statements go here
COMMIT TRANSACTION;
-- Oh, look! A transaction with nine lives! 🐱‍👤

2. Double-checking with unique constraints

Unique constraints keep your data kiara (clean) and prevent duplicates from sneaking in:

ALTER TABLE YourTable
ADD CONSTRAINT UC_YourColumn UNIQUE (YourColumn);
-- Duplicates? Sorry, we don't do that here. 😎

3. Client-side error handling

For extra control, deal with duplicate key errors on the client side:

try
{
    // Execute SQL Insert command
}
catch (SqlException ex)
{
    if (ex.Number == 2627)
        // Is it a bird? Is it a plane? No, it's a duplicate key error!
    else
        throw; // Re-throw non-duplicate key errors
}