JOIN Type in MERGE ON clause


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:

http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/


 
 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



 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s