Mahmoud ElMansy: knowledge meant to be free

SQL SERVER Merge Statement Make INSERT UPDATE DELETE To Integrate Data Between Two Tables

In this post i will not talk long i will put code and examples for how to use Merge Statement.

 Go
MERGE
[ INTO ] target_table [ WITH ( ) ] [ [ AS ] table_alias]
USING ON
[ WHEN MATCHED [ AND ]
THEN ]
[ WHEN NOT MATCHED [ BY TARGET ] [ AND ]
THEN ]
[ WHEN NOT MATCHED BY SOURCE [ AND ]
THEN ]
[ ]
[ OPTION ( [ ,...n ] ) ]
;

Note ; is Mandatory

Now Examples

USE tempdb;
GO
CREATE TABLE dbo.Target(EmployeeID INT, EmployeeName VARCHAR(10),
CONSTRAINT Target_PK PRIMARY KEY(EmployeeID));
CREATE TABLE dbo.SOURCE(EmployeeID INT, EmployeeName VARCHAR(10),
CONSTRAINT Source_PK PRIMARY KEY(EmployeeID));
GO
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(100, 'Mary');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(101, 'Sara');
INSERT dbo.Target(EmployeeID, EmployeeName) VALUES(102, 'Stefano');

 

GO
INSERT dbo.SOURCE(EmployeeID, EmployeeName) VALUES(103, 'Bob');
INSERT dbo.SOURCE(EmployeeID, EmployeeName) VALUES(104, 'Steve');
GO
-- MERGE statement with the join conditions specified correctly.
USE tempdb;
GO
BEGIN TRAN;
MERGE Target AS T
USING SOURCE AS S
ON (T.EmployeeID = S.EmployeeID)
WHEN NOT MATCHED BY TARGET AND S.EmployeeName LIKE 'S%'
THEN INSERT(EmployeeID, EmployeeName) VALUES(S.EmployeeID, S.EmployeeName)
WHEN MATCHED
THEN UPDATE SET T.EmployeeName = S.EmployeeName
WHEN NOT MATCHED BY SOURCE AND T.EmployeeName LIKE 'S%'
THEN DELETE
OUTPUT $action, inserted.*, deleted.*;
ROLLBACK TRAN;
GO

This is simple example full article can be found
atInserting, Updating, and Deleting Data by Using MERGE