SQL_MERGE

USE tempdb;
GO
CREATE TABLE dbo.TargetData(EmployeeID int, EmployeeName varchar(10),
     CONSTRAINT Target_PK PRIMARY KEY(EmployeeID));
CREATE TABLE dbo.SourceData(EmployeeID int, EmployeeName varchar(10),
     CONSTRAINT Source_PK PRIMARY KEY(EmployeeID));
GO
INSERT dbo.TargetData(EmployeeID, EmployeeName) VALUES(100, 'Mary');
INSERT dbo.TargetData(EmployeeID, EmployeeName) VALUES(101, 'Sara');
INSERT dbo.TargetData(EmployeeID, EmployeeName) VALUES(102, 'Stefano');

GO
INSERT dbo.SourceData(EmployeeID, EmployeeName) Values(101, 'Bob');
INSERT dbo.SourceData(EmployeeID, EmployeeName) Values(104, 'Steve');
GO

--BEGIN TRAN
--
MERGE TargetData T
USING SourceData S
ON T.EmployeeID = S.EmployeeID

 --Not Exist in Target
WHEN NOT MATCHED /* AND AND S.EmployeeName LIKE 'S%' */ BY Target
THEN INSERT  VALUES(S.EmployeeID, S.EmployeeName)  -- Inserts to Target
 --Not Exist in Target
WHEN NOT MATCHED /* AND AND S.EmployeeName LIKE 'S%' */ BY Source
THEN DELETE  -- Inserts to Target
WHEN MATCHED THEN UPDATE SET T.EmployeeName = S.EmployeeName

OUTPUT $action, inserted.*,deleted.*;
--updated.*;


select * from dbo.TargetData
select * from dbo.SourceData
/*
DROP TABLE dbo.TargetData
DROP TABLE dbo.SourceData
*/

--COMMIT TRAN
--ROLLBACK TRAN