Saturday, 20 May 2017

Update existing record and insert new record into table based on another table using MERGE in SQL-Server

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.

tblEmployee <--- tblTemptblEmployee
Insert and update record using MERGE in sql server Using MERGE in sql server

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.

Share:

2 comments:

Email Subscription

Subscribe to our newsletter to get the latest articles directly into your inbox

Blog Archive

BUY FROM AMAZON