JOIN Type in MERGE ON clausePosted: February 9, 2015
DO your know what kind of JOIN is in Merge Statement? It depends on how WHEN clause is used in your MERGE.
It is a simple INNER JOIN when yo have only one WHEN MATCHED clause;
WHen you have One MATCHE and one WHEN NOT Matched clause , the join is LEFT OUTER JOIN;
WHen you have One MATCHE and two WHEN NOT Matched clause, the join is FULL OUTER JOIN;
A MERGE sample snippet with (HOLDLOCK) hint is recommended by Aaron Bertrand:
If (object_id('dbo.mytest','U') is not null) drop table dbo.mytest; If (object_id('dbo.mylog','U') is not null) drop table dbo.mylog; CREATE TABLE dbo.mytest(id INT); CREATE TABLE dbo.myLog(action varchar(10), idnew int, idold int); GO INSERT dbo.mytest VALUES(1),(5); GO select * from mytest --Check Requests Log INSERT INTO dbo.mylog (action, idNew, idOld) SELECT action, idNew, idOld FROM ( MERGE dbo.mytest WITH (HOLDLOCK) AS Target USING (VALUES(1),(2),(3)) AS Source(id) ON Target.id = Source.id WHEN MATCHED THEN UPDATE SET Target.id = Source.id WHEN NOT MATCHED THEN INSERT(id) VALUES(Source.id) WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT $action , inserted.id , deleted.id ) AS Changes (Action, idNew, idOld) ; select * from mylog select * from mytest