In SQL Server, an “UPSERT” operation combines the actions of both an INSERT and an UPDATE statement into a single statement. This operation inserts a new row into a table if that row is not present. Otherwise, it updates an already present row. The benefit of performing an UPSERT operation is that it eliminates the need for separate insert and update statements, simplifying your SQL code and reducing the likelihood of errors.
Creating a Table
Let’s create a table in the SQL Server that we can use to perform the UPSERT operations on.
[EmpID] [smallint] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[DepID] [smallint] NULL,
[Sal] [int] NULL
)
INSERT INTO [dbo].[Employee] ([EmpID], [Name], [DepID], [Sal])
VALUES
(1, 'Moumita', 5, 50000),
(2, 'Orijit', 2, 30000),
(3, 'Somdeb', 5, 60000),
(4, 'Rocky', 2, 50000),
Select * FROM [dbo].[Employee];
Output:
1 Moumita 5 50000
2 Orijit 2 30000
3 Somdeb 5 60000
4 Rocky 2 50000
Ways to Perform the UPSERT Operation
There are several ways to perform an UPSERT operation in the SQL Server. In this article, we will discuss the three common methods to perform an UPSERT operation: using the EXISTS clause, the ROWCOUNT function, and the MERGE statement.
1. Using the EXISTS Clause
This method involves using a subquery to check if the row already exists in the table before performing an insert or update statement. The EXISTS clause returns true if the subquery returns any rows.
declare @EmployeeID int = 17;
declare @Name varchar(max) = 'Bidisha';
IF EXISTS ( Select * from dbo.Employee with (UPDLOCK, SERIALIZABLE)
where EmpID = @EmployeeID)
Update dbo.EMployee
Set Name = @Name
Where EmpID = @EmployeeID
ELSE
INSERT into dbo.Employee (EmpID, Name)
VALUES
(@EmployeeID, @Name)
COMMIT TRANSACTION;
Output:
17 Bidisha NULL NULL
Here, the SQL code uses the EXISTS method to perform an UPSERT operation in the SQL Server. It initiates a transaction and declares the variables to hold the employee ID and name values. The code checks if a record with the given employee ID exists in the table using the EXISTS clause. If it exists, it updates the employee’s name to a new value. Otherwise, it inserts a new row with the given employee ID and name.
2. Using the ROWCOUNT Function
This method involves running an update statement first, followed by an insert statement if the update statement didn’t update any rows.
declare @EmployeeID int = 18;
declare @Name varchar(max) = 'Sonalika';
Update dbo.EMployee with (UPDLOCK, SERIALIZABLE)
Set Name = @Name
Where EmpID = @EmployeeID
IF @@ROWCOUNT = 0
INSERT into dbo.Employee (EmpID, Name)
VALUES
(@EmployeeID, @Name)
COMMIT TRANSACTION;
Output:
18 Sonalika NULL NULL
Here, the SQL code attempts to update the name of the employee with the given ID in the table using the UPDLOCK and SERIALIZABLE hints which acquire an exclusive lock on the selected row. If the update affects no rows, the code performs an INSERT statement to insert a new row with the given employee ID and name values.
3. Using the MERGE Statement
A third way to perform an UPSERT operation in the SQL Server is to apply the MERGE statement. This method combines the actions of the insert and update statements into a single statement, similar to the EXISTS clause but with more advanced features.
declare @Name varchar(max) = 'Arnab';
Merge dbo.Employee WITH (HOLDLOCK) as Target
USING (VALUES (@EmployeeID, @Name)) as Source(EmployeeID, Name)
ON Target.EMpID = Source.EMployeeID
WHEN MATCHED THEN UPDATE Set Target.Name = Source.Name
WHEN NOT MATCHED THEN INSERT (EmpID, Name) VALUES (Source.EMployeeID, Source.Name);
Output:
18 Arnab NULL NULL
This SQL query uses the MERGE statement to merge the data from a source table into a target table. It matches the rows between the two tables using the “EmpID column”, and updates the “Name column” in the target table if a match is found. If there is no match, it inserts a new row.
Conclusion
The UPSERT operations are a common requirement in the SQL Server. There are multiple ways to achieve them, including using the EXISTS method, the ROWCOUNT function, and the MERGE statement. We can pick the best approach based on our requirement.
