In this article, I am going to explain you how to update a Record in a Table based on another table using SQL-Server MERGE statement. If record is already present in destination table then we will update the record into destination with source table based on common key column and if record is not present then we will insert new record into destination table.

Consider the below image, here we have two table "tblEmployee" and "tblTemp", we need to update Name column and insert two new records in tblEmployee from tblTemp using MERGE.

Below is the script to create two table and insert some dummy records.

CREATE TABLE tblEmployee
(
Id INT,
Name VARCHAR(50)
)
CREATE TABLE tblTemp
(
Id INT,
Name VARCHAR(50)
)
go
INSERT INTO tblEmployee VALUES(1,'Employee 1')
INSERT INTO tblEmployee VALUES(2,'Employee 2')
INSERT INTO tblEmployee VALUES(3,'Employee 3')

INSERT INTO tblTemp VALUES(3,'Updated Employee')
INSERT INTO tblTemp VALUES(4,'Employee 4')
INSERT INTO tblTemp VALUES(5,'Employee 5')

Using WHEN MATCHED clause

MERGE INTO tblEmployee e
USING tblTemp t on e.Id=t.Id
WHEN MATCHED THEN
UPDATE SET e.Name=t.name;

SELECT * FROM tblEmployee

Using WHEN MATCHED AND WHEN NOT MATCHED clause

MERGE INTO tblEmployee e
USING tblTemp t on e.Id=t.Id
WHEN MATCHED THEN
UPDATE SET e.Name=t.name 
WHEN NOT MATCHED THEN
INSERT (Id,Name) VALUES(t.Id,t.Name);

SELECT * FROM tblEmployee

In later article, I will discuss delete clause also.