SqlServer Upserts

Sometimes you want to save a record, but it may be an existing record (UPDATE) or a new one (INSERT). The pattern is sometimes called an “upsert” (update/insert).

You could try to do this the standard way you would via an ORM (SELECT to see if it exists, if it does, UPDATE, else INSERT). But if other users are updating at the same time, you will see concurrency errors or deadlocks.

First, let’s look at simpler SQL that is vulnerable to concurrency errors, than two ways of doing it safely.

Simple (not concurrent-safe!!)

UPDATE then check @@ROWCOUNT and INSERT if necessary. Only use this when the same record will not be created by two sources.

DECLARE @CategoryName NVARCHAR(15) = 'Dairy';
DECLARE @Description NVARCHAR(MAX) = 'Milk, cheese and yoghurts';
DECLARE @Id int = null;

UPDATE [Categories]
    SET [Description] = @Description,
        @Id = [CategoryID]
    WHERE [CategoryName] = @CategoryName;

IF @@ROWCOUNT = 0
    INSERT INTO [Categories]
               ([CategoryName]
               ,[Description])
         VALUES
               (@CategoryName
               ,@Description);
--if id is not set in UPDATE, then grab scope identity
SET @Id = ISNULL(@Id, CAST(SCOPE_IDENTITY() AS int));
--select it out
SELECT @Id AS Id;

This example grabs the affected Id too (whether identity insert or update).

Concurrent-safe

A more conventional IF NOT EXISTS... INSERT - ELSE - UPDATE. The lock hints protect for concurrency. The UPDLOCK and SERIALIZABLE hints are as suggested in this Sam Saffron blog post from 2007

IF NOT EXISTS(
        SELECT * FROM [Categories] WITH ( UPDLOCK, SERIALIZABLE )
        WHERE [CategoryName] = @CategoryName )
    BEGIN
        INSERT INTO [Categories]
                   ([CategoryName]
                   ,[Description])
             VALUES
                   (@CategoryName
                   ,@Description);
        SET @Id = CAST(SCOPE_IDENTITY() AS int);
    END
ELSE
    BEGIN
        UPDATE [Categories]
            SET [Description] = @Description,
                @Id = [CategoryID]
        WHERE [CategoryName] = @CategoryName;
    END

SELECT @Id AS Id;

MERGE

Much the same as before, using the MERGE command. MERGE by itself is not concurrent-safe; you must still use lock hints.

MERGE INTO [Categories] WITH ( UPDLOCK, SERIALIZABLE ) AS target
    --if/where part
    USING
        (SELECT @CategoryName, @Description ) AS source
            ([CategoryName], [Description])
        ON (target.CategoryName = source.CategoryName)
    --found, so update
    WHEN MATCHED THEN
        UPDATE SET [Description] = @Description,
                @Id = [CategoryID]
    --not found, so insert
    WHEN NOT MATCHED THEN
        INSERT ([CategoryName]
                   ,[Description])
             VALUES
                   (@CategoryName
                   ,@Description);

SET @Id = ISNULL(@Id, CAST(SCOPE_IDENTITY() AS int));

SELECT @Id AS Id;

Other Databases

MySQL has INSERT … ON DUPLICATE KEY UPDATE … and SELECT … FOR UPDATE.

Oracle has SELECT FOR UPDATE cursors

static void