static void

SqlServer Upserts

Published Wednesday 12 February 2014

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.

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;