sql2008-merge(insert,update,delete)

IF OBJECT_ID ('tempdb..#Departments') IS NOT NULL
DROP TABLE #Departments;
GO
CREATE TABLE #Departments
(
DeptID tinyint NOT NULL PRIMARY KEY,
DeptName nvarchar(30),
Manager nvarchar(50)
);
GO
INSERT INTO #Departments
VALUES
(1, 'Human Resources', 'Margheim'),
(2, 'Sales', 'Byham'),
(3, 'Finance', 'Gill'),
(4, 'Purchasing', 'Barber'),
(5, 'Manufacturing', 'Brewer');

IF OBJECT_ID ('tempdb..#Departments_delta') IS NOT NULL
    DROP TABLE #Departments_delta;
GO
CREATE TABLE #Departments_delta
(
DeptID tinyint NOT NULL PRIMARY KEY,
DeptName nvarchar(30),
Manager nvarchar(50)
);
GO
INSERT INTO #Departments_delta VALUES
    (1, 'Human Resources', 'Margheim'), (2, 'Sales', 'Erickson'),
    (3 , 'Accounting', 'Varkey'),(4, 'Purchasing', 'Barber'),
    (6, 'Production', 'Jones'), (7, 'Customer Relations', 'Smith');
GO

select * from #Departments
select * from #Departments_delta

MERGE #Departments AS d
USING #Departments_delta AS dd
ON (d.DeptID = dd.DeptID)
WHEN MATCHED AND d.Manager <> dd.Manager OR d.DeptName <> dd.DeptName
    THEN UPDATE SET d.Manager = dd.Manager, d.DeptName = dd.DeptName
WHEN NOT MATCHED THEN
    INSERT (DeptID, DeptName, Manager)
    VALUES (dd.DeptID, dd.DeptName, dd.Manager)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT $action,
       inserted.DeptID AS SourceDeptID,
       inserted.DeptName AS SourceDeptName,
       inserted.Manager AS SourceManager,
       deleted.DeptID AS TargetDeptID,
       deleted.DeptName AS TargetDeptName,
       deleted.Manager AS TargetManager;   

 

select * from #Departments