Insert Update stored proc on SQL Server

sql

upsert

performance-tuning

best-practices

byNikita Barsukov·Jan 6, 2025

Executing MERGE in SQL Server performs upsert actions, allowing us to either update the pre-existing rows or insert new ones with a single line of script:

MERGE TargetTable AS T
USING SourceTable AS S
ON T.Key = S.Key
WHEN MATCHED THEN
    UPDATE SET T.Value = S.Value
WHEN NOT MATCHED THEN
    INSERT (Key, Value) VALUES (S.Key, S.Value);

This makes MERGE the go-to option for an efficient conditional action on record existence.

Nailing the Upsert: Best Practices

Prioritizing Updates: Better for Performance

A strategy where update is your first move helps limit the number of total table reads. Anticipating a record's existence, we execute update and then use @@rowcount to see whether an insert is necessary:

UPDATE TargetTable               -- Given we owe money to Murphy’s Law…
SET Value = S.Value              -- ...let’s pay by updating first 
FROM SourceTable AS S
WHERE TargetTable.Key = S.Key;

IF @@ROWCOUNT = 0
BEGIN                            -- An imaginary drumroll please…
    INSERT INTO TargetTable (Key, Value)
    SELECT Key, Value
    FROM SourceTable
    WHERE NOT EXISTS (
        SELECT 1 FROM TargetTable WHERE Key = SourceTable.Key
    );                          -- … and, it’s an insert!
END;

Playing Nice with Concurrency

In situations where multiple processes might access your data simultaneously, wrapping your upsert in a transaction with appropriate lock hints helps keep your data's integrity intact:

BEGIN TRANSACTION;

MERGE TargetTable WITH (HOLDLOCK) AS T  -- Be kind, rewind… and lock.
...

COMMIT TRANSACTION;

Also, consider using the SERIALIZABLE isolation level when dealing with concurrent accesses.

Crafty Error Handling is a Lifesaver

Always ensure error handling strategies are in place to rollback transactions if things go south. This action is crucial to keep data integrity fighting fit:

BEGIN TRY
    BEGIN TRANSACTION;                 -- A crazy train called ‘try’
    -- Upsert logic here
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;              -- …meets a wall called ‘catch’
    -- Handle error
END CATCH;

The Existential Crisis of Existence Checks

Sometimes an existence check might be necessary, but is the overhead added by SELECT justifiable? Will the logic of the check align seamlessly with your procedure's intentions? Mull over these possibilities.

Advanced Tactics for Upsert Champions

Performance Tuning for Upsert

Every upsert strategy brings along a unique set of performance implications. While the MERGE statement is potent, it might pack quite a punch in terms of performance if misused. Keep an eye on your query plans and stats to ensure it's the right tool for your job.

Staying a Step Ahead with SQL Server

New features that optimize upsert operations are continuously rolled out in SQL Server. Keep pace with SQL Server versions and their offerings to fine-tune your upsert process and benefit from syntax upgrades and performance enhancements.

Community Knowledge is Power

Explore blogs, forums, and other community resources to gain insights into real-life problem-solving experiences with upsert intricacies in advanced production settings.

explain-codes / Sql / Insert Update stored proc on SQL Server

Linked

Insert ... ON DUPLICATE KEY (do nothing)



How to iterate over results of a query



Mysql INSERT INTO ... VALUES and SELECT



Update or Insert (multiple rows and columns) from subquery in PostgreSQL



Solutions for INSERT OR UPDATE on SQL Server

