Safe update-or-insert patterns

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Update a keyed row or insert it under a serializable key-range lock. Run changing examples only on a disposable training instance. The example uses a session-local object or rolls back its changes.

Run the example

BEGIN TRANSACTION;
UPDATE dbo.Categories WITH (UPDLOCK, SERIALIZABLE)
SET CategoryName = N'Practice category' WHERE CategoryID = 99;
IF @@ROWCOUNT = 0
    INSERT INTO dbo.Categories (CategoryID, CategoryName)
    VALUES (99, N'Practice category');
SELECT CategoryID, CategoryName FROM dbo.Categories WHERE CategoryID = 99;
ROLLBACK TRANSACTION;

Read it step by step

1. The UPDATE targets the unique CategoryID key. WITH (UPDLOCK, SERIALIZABLE) are table hints: UPDLOCK reserves the matching row for this update, and SERIALIZABLE also protects the key range, so another session cannot insert CategoryID 99 until this transaction ends. Chapter 11 explains locks and isolation levels in full.
2. @@ROWCOUNT is a built-in value holding the number of rows the previous statement affected. It is read immediately, before another statement changes it. IF @@ROWCOUNT = 0 runs the INSERT only when the UPDATE found no row.
3. The transaction keeps the decision and write together; ROLLBACK removes the practice row.

Check the result

CategoryID 99 is absent in the unmodified dataset, so this run takes the INSERT branch.

Practice

Use the same pattern for existing CategoryID 1, temporarily changing its name to Practice existing.

BEGIN TRANSACTION;
UPDATE dbo.Categories WITH (UPDLOCK, SERIALIZABLE)
SET CategoryName = N'Practice existing' WHERE CategoryID = 1;
IF @@ROWCOUNT = 0
    INSERT INTO dbo.Categories (CategoryID, CategoryName)
    VALUES (1, N'Practice existing');
SELECT CategoryID, CategoryName FROM dbo.Categories WHERE CategoryID = 1;
ROLLBACK TRANSACTION;