IT Nota

  • Home
  • How To
  • .NET
  • WordPress
  • Contact
Home » Database » Ways to Upsert a Record in SQL Server

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)

July 8, 2014 Filed Under: Database Tagged With: SQL, SQL Server

Comments

  1. Ali says

    March 24, 2019 at 2:31 am

    Thank you

    Reply
    • platt says

      March 28, 2019 at 9:33 am

      You’re welcome Ali.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • .NET
  • Coding
  • Database
  • How To
  • Internet
  • Multimedia
  • Photography
  • Resources
  • Review
  • WordPress
  • Writing

Recent Posts

  • How to Use Custom Color in SSMS Using Redgate SQL Prompt
  • How to Install Python on Windows Server
  • How to Enable Secure HttpOnly Cookies in IIS
  • How to Create iCloud Mail Email Address
  • How to Import IIS Log to PostgreSQL
StudioPress Theme of the Month

Recent Posts

  • How to Use Custom Color in SSMS Using Redgate SQL Prompt
  • How to Install Python on Windows Server
  • How to Enable Secure HttpOnly Cookies in IIS
  • How to Create iCloud Mail Email Address
  • How to Import IIS Log to PostgreSQL
  • RSS

Tags

.NET Core Access Adobe AdSense Amazon ASP.NET Cdonts Dll Classic ASP Code Editor Connect-It Copywriting ETL Genesis Framework Git Google HP Asset Manager HTML HTML5 Hugo IIS Information Security Internet Internet Information Services iOS Linux macOS Microsoft Microsoft SQL Server MVC Nikon Oracle PHP Simple Mail Transfer Protocol Smtp Server Social Media SQL SQL Server SSIS SSMS SSRS Windows Windows 8 Windows 10 Windows 2012 Windows Server

Copyright © 2011-2019 IT Nota. All rights reserved. Terms of Use | Privacy Policy | Disclosure