Ways to Upsert a Record in SQL Server

To continue the previous post, this article demonstrates ways to do Upsert (update and insert) and how MERGE statement in SQL Server 2008 (or later) can be more efficient to perform the two operations at once.

First we’ll create a table for this demo.

CREATE TABLE dbo.GroupInfo (
    Id    int unique not null
  , App   varchar(100)
  , DB    bit
)

We want to do update if the Id is found on the table and insert if it’s a new Id number.

1. Conventional way of doing it is by using IF EXISTS statement.

CREATE PROCEDURE [dbo].[p_UPSERT1]
    @ID     int
  , @APP    varchar(100)
  , @DB     bit
AS
SET NOCOUNT ON;
IF EXISTS (SELECT Id FROM dbo.GroupInfo WHERE Id = @ID)
  UPDATE dbo.GroupInfo
  SET
      App = @APP
    , DB = @DB
  WHERE Id = @ID
ELSE
  INSERT INTO dbo.GroupInfo (
      Id
    , App
    , DB
  ) VALUES (
        @ID
    , @APP
    , @DB
  )
  SET NOCOUNT OFF;

2. Second way of doing it is by taking advantage of the @@ROWCOUNT.

CREATE PROCEDURE [dbo].[p_UPSERT2]
    @ID     int
  , @APP    varchar(100)
  , @DB     bit
AS
SET NOCOUNT ON;
UPDATE dbo.GroupInfo
  SET
      App = @APP
    , DB = @DB
WHERE Id = @ID
IF @@ROWCOUNT = 0
  INSERT INTO dbo.GroupInfo (
      Id
    , App
    , DB
  ) VALUES (
      @ID
    , @APP
    , @DB
  )
SET NOCOUNT OFF;

3. The third and probaby the best way by using MERGE to perform INSERT and UPDATE operations on a table in a single statement.

CREATE PROCEDURE [dbo].[p_UPSERT3]
    @ID     int
  , @APP    varchar(100)
  , @DB     bit
AS
SET NOCOUNT ON;
MERGE INTO dbo.GroupInfo AS tgt
USING
  (SELECT @ID) AS src (id)
  ON tgt.Id = src.id
WHEN MATCHED THEN
  UPDATE        
    SET
        App = @APP
      , DB = @DB
WHEN NOT MATCHED THEN
  INSERT (
      Id
    , App
    , DB
  ) VALUES (
      @ID
    , @APP
    , @DB
  );
SET NOCOUNT OFF;

Now, you can analyze the execution plan of each stored procedure on your own to compare them.

EXEC p_UPSERT1
  @ID = 1
, @APP = 'App 1'
, @DB = 0
GO

EXEC p_UPSERT2
  @ID = 2
, @APP = 'App 2'
, @DB = 0
GO

EXEC p_UPSERT3
  @ID = 3
, @APP = 'App 3'
, @DB = 0
GO

Further Reading

SQL: If Exists Update Else Insert
MERGE (Transact-SQL)