SQL - 3 ways to perform Upsert

In this tutorial, we discuss 3 ways to perform UPSERT in SQL Server. 

All the methods are discussed in detail in the video tutorial here -

https://youtu.be/3VO1AhrIz8M

The sample data for executing the queries can be created as below -

CREATE TABLE [dbo].[Employee](

[EmployeeID] [smallint] NOT NULL,

[Name] [nvarchar](50) NOT NULL,

[DeptID] [smallint] NULL,

[Salary] [int] NULL

) 

INSERT INTO [dbo].[Employee] ([EmployeeID],[Name],[DeptID],[Salary]) VALUES (1, 'Mia', 5, 50000);

INSERT INTO [dbo].[Employee] ([EmployeeID],[Name],[DeptID],[Salary]) VALUES (2, 'Adam', 2, 50000);

INSERT INTO [dbo].[Employee] ([EmployeeID],[Name],[DeptID],[Salary]) VALUES (3, 'Sean', 5, 60000);

INSERT INTO [dbo].[Employee] ([EmployeeID],[Name],[DeptID],[Salary]) VALUES (4, 'Robert', 2, 50000);

INSERT INTO [dbo].[Employee] ([EmployeeID],[Name],[DeptID],[Salary]) VALUES (5, 'Jack', 2, 45000);

INSERT INTO [dbo].[Employee] ([EmployeeID],[Name],[DeptID],[Salary]) VALUES (6, 'Neo', 5, 60000);

INSERT INTO [dbo].[Employee] ([EmployeeID],[Name],[DeptID],[Salary]) VALUES (7, 'Jennifer', 2, 55000);

INSERT INTO [dbo].[Employee] ([EmployeeID],[Name],[DeptID],[Salary]) VALUES (8, 'Lisa', 2, 85000);

INSERT INTO [dbo].[Employee] ([EmployeeID],[Name],[DeptID],[Salary]) VALUES (9, 'Martin', 2, 35000);

INSERT INTO [dbo].[Employee] ([EmployeeID],[Name],[DeptID],[Salary]) VALUES (10, 'Brad', 5, 90000);

INSERT INTO [dbo].[Employee] ([EmployeeID],[Name],[DeptID],[Salary]) VALUES (11, 'Cathy', 2, 60000);

INSERT INTO [dbo].[Employee] ([EmployeeID],[Name],[DeptID],[Salary]) VALUES (12, 'David', 5, 60000);

Below is the summary of the 3 methods -

Method 1 - EXISTS

Here we first check if the record already exists in the table. If it does, we perform update else insert.

Since, there are two queries been executed here, the table is scanned twice thereby impacting the performance.

SQL Query -

--Select * from dbo.Employee where EmployeeID = 16

BEGIN TRANSACTION

declare @EmployeeID int = 17;

declare @Name varchar(max) = 'Brad';

IF EXISTS ( Select * from dbo.Employee with (UPDLOCK, SERIALIZABLE)

where EmployeeID = @EmployeeID)

Update dbo.EMployee 

Set Name = @Name

Where EmployeeID = @EmployeeID

ELSE 

INSERT into dbo.Employee (EmployeeID, Name)

VALUES 

(@EmployeeID, @Name)

COMMIT TRANSACTION;

Method 2 - ROWCOUNT

Here we directly perform an Update. If the operation is successful, the @@RowCount is set to 1 else 0.

In the next step, we check the value of @@RowCount. Only if the value is 0, the Insert is performed.

SQL Query -

--Select * from dbo.Employee where EmployeeID = 18

BEGIN TRANSACTION

declare @EmployeeID int = 18;

declare @Name varchar(max) = 'Anita1';

--IF EXISTS ( Select * from dbo.Employee with (UPDLOCK, SERIALIZABLE)

--where EmployeeID = @EmployeeID)

Update dbo.EMployee with (UPDLOCK, SERIALIZABLE) 

Set Name = @Name

Where EmployeeID = @EmployeeID

IF @@ROWCOUNT = 0  

INSERT into dbo.Employee (EmployeeID, Name)

VALUES 

(@EmployeeID, @Name)

COMMIT TRANSACTION;

Method 3 - Merge

Here we use the Merge statement and use its features to perform either an Update (When key matched) or Insert (When key not matched) operation.

SQL Query -

--Select * from dbo.Employee where EmployeeID = 20

declare @EmployeeID int = 20;

declare @Name varchar(max) = 'Amy';

Merge dbo.Employee WITH (HOLDLOCK) as Target 

USING (VALUES (@EmployeeID, @Name)) as Source(EmployeeID, Name) 

ON Target.EMployeeID = Source.EMployeeID

WHEN MATCHED THEN UPDATE Set Target.Name = Source.Name

WHEN NOT MATCHED THEN INSERT (EmployeeID, Name) VALUES (Source.EMployeeID, Source.Name);

Microsoft Documentation for UPDLOCK, HOLDLOCK and SERIALIZABLE -

https://docs.microsoft.com/en-us/answers/questions/99453/what-is-the-difference-between-rowlock-updlock-and.html#:~:text=UPDLOCK%20uses%20an%20update%20lock,of%20the%20statement%20or%20transaction.

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver16