Sql Server Insert if not exists

How to tackle Race conditions

Understand that race conditions can occur if another transaction inserts the same data between the existence check and INSERT operation. To mitigate race conditions, consider using transaction isolation levels or table locks:

BEGIN TRANSACTION;
-- They see me rollin'
IF NOT EXISTS (SELECT * FROM YourTable WHERE SomeColumn = SomeValue)
BEGIN
    INSERT INTO YourTable (ColumnO1, Column2) VALUES (Value1, Value2);
    -- Insert me, I'm famous 🌟
END;
COMMIT TRANSACTION;


Using MERGE for conditional insertion

Embrace the power of MERGE, which can also be used for a conditional insert:

MERGE INTO YourTable AS Target
USING (VALUES (Value1, Value2)) AS Source (Column1, Column2)
ON Target.SomeColumn = Source.SomeColumn
WHEN NOT MATCHED THEN
    -- Merge but don't purge 🙅‍
    INSERT (Column1, Column2)
    VALUES (Source.Column1, Source.Column2);


Please be cautious about performance implications when dealing with MERGE in high concurrency environments.

Combating duplication with subqueries and joins

When the existence check is not so straightforward, a subquery or LEFT JOIN can come in handy:

INSERT INTO YourTable (Column1, Column2, ...)
SELECT temp.Column1, temp.Column2, ...
FROM (
    SELECT Value1 AS Column1, Value2 AS Column2, ...
) AS temp
LEFT JOIN YourTable ON YourTable.SomeColumn = temp.Column1
WHERE YourTable.SomeColumn IS NULL;
-- Join here, it's null 🙌


For the big boys (large datasets)

When dealing with hefty hogs aka large datasets, consider benchmarking your insertion techniques for performance optimization:

BEGIN TRANSACTION;
-- Bigger the dataset, higher the stakes 🎲
IF NOT EXISTS (SELECT * FROM YourTable WHERE SomeColumn = SomeValue)
BEGIN
    INSERT INTO YourTable (ColumnO1, Column2) VALUES (Value1, Value2);
END;
COMMIT TRANSACTION;


Tailoring to real-world scenarios

In reality, INSERT if not exists might become part of a bigger operation such as a stored procedure or a transaction where multiple tables are involved:

CREATE PROCEDURE InsertIfNotExists
    @Column1Value Type1,
    @Column2Value Type2
AS
BEGIN
    IF NOT EXISTS (SELECT 1 FROM YourTable WHERE Column1 = @Column1Value)
    BEGIN
        -- Insert 'em all, and let SQL Server sort 'em out 💪
        INSERT INTO YourTable (Column1, Column2)
        VALUES (@Column1Value, @Column2Value);
    END;
END;


Sharpen your blade with EXCEPT

For scenarios where performance matters, consider using the EXCEPT operator to exclude existing records during insertion:

INSERT INTO YourTable (Column1, Column2)
SELECT Column1, Column2
FROM (
    SELECT Value1, Value2 
    EXCEPT 
    SELECT Column1, Column2 FROM YourTable
) AS Data
-- Frankly, my dear, I don't give an insert 🎩
