Merge 2- SQL Server 2008


I have given basic details about merge command in my first article


Merge SQL Server 2008



Now we will go some more advance in it. As we know we can do all update /insert / delete operation on a single table using merge command in a single query. Now using output we can store all this details. Also SQL Server 2008 supports new command $action which will give us information about operation done on the table.
Here we will store inserted / updated / deleted data in a separate table using output clause. Also we will store action related to that data.



DECLARE @t1 TABLE(

id INT PRIMARY KEY,

code VARCHAR(10)

)


DECLARE @t2 TABLE(

id INT PRIMARY KEY,

code VARCHAR(10)

)


DECLARE @t1changed TABLE(

ACTION VARCHAR(51),

deleted_id INT,

deleted_code VARCHAR(10),

inserted_id INT,

inserted_code VARCHAR(10)

)


INSERT INTO @t1

VALUES (1,'a1'),

(2,'a2'),

(3,'a3'),

(4,'a4'),

(5,'a5'),

(6,'a6')


INSERT INTO @t2

VALUES (1,'a1'),

(3,'a3'),

(5,'b5'),

(7,'b7')



INSERT INTO @t1changed

(ACTION,

deleted_id,

deleted_code,

inserted_id,

inserted_code)

SELECT ACTION,

deleted_id,

deleted_code,

inserted_id,

inserted_name

FROM

(

MERGE @t1 as t1

USING @t2 as t2

ON t1.id = t2.id

WHEN MATCHED AND t1.code<> t2.code THEN

UPDATE SET t1.code = t2.code

WHEN NOT MATCHED BY TARGET THEN

INSERT VALUES(t2.id, t2.code)

WHEN NOT MATCHED BY SOURCE THEN

DELETE

OUTPUT $action, deleted.id , deleted.code,inserted.id,inserted.code

)AS T(action, deleted_id, deleted_code,inserted_id,inserted_name)





SELECT * FROM @t1changed