Safe solutions for INSERT OR UPDATE on SQL Server 2016

Question

Assume a table structure of MyTable(MyTableId NVARCHAR(MAX) PRIMARY KEY, NumberOfInserts INTEGER).

I often need to either update i.e. increment a counter of an existing record, or insert a new record if it doesn't exist with a value of 0 for NumberOfInserts.

Essentially:

IF (MyTableId exists)
    run UPDATE command
ELSE
    run INSERT command

My concern is losing data due to race conditions, etc.

What's the safest way to write this?

I need it to be 100% accurate if possible, and willing to sacrifice speed where necessary.

Answer 1

MERGE statement can perform both UPDATE and INSERT (and DELETE if needed).

Even though it is a single atomic statement, it is important to use HOLDLOCK query hint to prevent race condition. There is a blog post “UPSERT” Race Condition With MERGE by Dan Guzman where he explains in great details how it works and provides a test script to verify it.

The query itself is straight-forward:

DECLARE @NewKey NVARCHAR(MAX) = ...;

MERGE INTO dbo.MyTable WITH (HOLDLOCK) AS Dst
USING 
(
    SELECT @NewKey AS NewKey
) AS Src
ON Src.NewKey = Dst.[Key]
WHEN MATCHED THEN
UPDATE
SET NumberOfInserts = NumberOfInserts + 1
WHEN NOT MATCHED THEN
INSERT
(
    [Key]
    ,NumberOfInserts
)
VALUES
(
    @NewKey
    ,0
);

---

Of course, you can also use explicit two-step approach with a separate check if a row exists and separate UPDATE and INSERT statements. Just make sure to wrap them all in a transaction with appropriate table locking hints.

See Conditional INSERT/UPDATE Race Condition by Dan Guzman for details.