Upsert Operation in SQL Server

An “upsert” operation in SQL Server is a combination of an UPDATE and an INSERT operation, which means that if a particular row already exists, it will be updated with new values and if it does not exist, a new row will be inserted. Hence upsert is a combination of the commands update and insert.

The upsert operation is mainly used when you want to insert a new row into a table, but if a row with the same values already exists, you want to update that row instead of inserting a new one. The upsert operation can save you time and effort because you don’t have to manually check if a row already exists before deciding whether to insert or update it. 

This operation is mainly useful in incrementally loading the data in the table. 

In SQL Server, the upsert operation can be performed using two methods.

Method 1: MERGE Statement

The MERGE statement allows you to join a source table (the table that contains the data you want to merge) with a target table (the table you want to update or insert data into) based on a specific condition, and then perform an action such as an update or insert based on the results of the join.

Syntax:

MERGE <target_table> AS target

USING <source_table> AS source

ON <join_condition>

WHEN MATCHED THEN

    <update_statement>

WHEN NOT MATCHED THEN

    <insert_statement>

WHEN NOT MATCHED BY SOURCE THEN

    <delete_statement>

Example:

let’s consider the table student which contains the columns Id, Name, and Age.

Step 1: Create student table, and insert values.

Query:

create table students
(
id  int primary key,
Name varchar(50) ,
age int default 18
)

insert into sample_Employee_upsert(id, Name,age)
values(1,'Pranjal',18),(2,'Amar',21),
(3,'Mahesh',22),(4,'Tushar',21),(5,'john',20)

 Step 2: Let’s see the student’s table:

Query:

select * from students

Output:

Step 3:  Use the merge command to upsert the records in the student table.

Query:

DECLARE @id INT=4,
@name varchar(50)='Harsh',
@age int =25
MERGE INTO students
USING (VALUES (@id, @name,@age)) AS source (ID, Name,Age)
ON students.ID = source.ID
WHEN MATCHED THEN
 UPDATE SET Name = source.Name,
 age=source.age
WHEN NOT MATCHED THEN
 INSERT (ID, Name,age)
 VALUES (source.ID, source.Name,source.age);

Output:

Now see the students table at Id =4 , Name =Tushar an Age=21 will get updated with Name =Harsh and Age= 25

Here you can see that the record at Id =4 gets updated because Id 4 already exists in the table hence it updates another column at Id 4 instead of inserting a new row.

Method 2: Using If-else Statement

Now we will consider the above same example and try to perform an upsert operation using the if_else statement.

Query:

DECLARE @id INT=15,
@name varchar(50)='Omkar',
@age int =23
IF ((select count(*) from students where ID=@id ) =1)
BEGIN
UPDATE students
 SET
     Name = @name,
  age= @age
 WHERE ID = @id;
END
ELSE
BEGIN
INSERT INTO students(ID,Name,age)
 VALUES (@id,@name,@age )
END

select * from students

Output:

Here you can see that the id=15 record gets inserted into the table students.

In conclusion, we can say that the upsert statement or commands are very useful in the SQL server to incrementally add  or update the data from the real-time source or static source, which is very useful for reducing the time and increasing the performance. Also if you use the upsert statement in the projects it mostly decreases the data duplication or redundancy.