Merge Me
using You 
on You.obidient=Me.obidient
When Matched 
Then Party time
When Not Matched By Me
Then Sorry 
When Not Matched By You
Then Unfortunate 

Viola Viola A quick and charming poem, Isn’t it? 

Merge clause may seem daunting at a first glance but with my quick tips you can master over the basic of merge clause syntax and usage. Follow on ..

Merge statement is used to make changes in one table based on the values matched from the other table. Merge statement can be handy to combine the three DML statements  insert, update and delete into one single statement. Each row of the source table is matched with the target table’s row.

Syntax:
MERGE targetTable
Using sourceTable
ON mergeCondition
WHEN MATCHED
THEN updateStatement
WHEN NOT MATCHED BY TARGET
THEN insertStatement
WHEN NOT MATCHED BY SOURCE
THEN deleteStatement

Elucidation

MERGE clause specifies a table or a view that is  the target of the insert, delete, update operations.

USING clause specifies the data source that is being joined with the target.

ON condition dictates the Merge to happen I. E in what columns or conditions the MERGE will occur.

WHEN clause determines the actions to be performed depending on the output of the ON condition. It can also be used to specify additional conditions.
A different combination of WHEN conditions yields different joins of these two tables.

When Matched———– INNER JOIN

WHEN NOT MATCHED BY TARGET [ WHEN MATCHED ] —-  LEFT OUTER JOIN

WHEN NOT MATCHED BY SOURCE [ WHEN MATCHED ] —-  RIGHT OUTER JOIN

WHEN NOT MATCHED BY TARGET &&
WHEN NOT MATCHED BY SOURCE [ WHEN MATCHED ] —-  FULL OUTER JOIN

WHEN NOT MATCHED BY TARGET —-  SEMI JOIN

WHEN NOT MATCHED BY SOURCE —-  ANTI SEMI JOIN

Exemplified
MERGE Candy
USING Chocolate
On Chocolate.type=Candy.type
WHEN MATCHED && Name like '%s'
UPDATE  Candy(price) OUTPUT INSERTED.price, DELETED. price VALUES(50)
WHEN NOT MATCHED BY TARGET
INSERT INTO  Candy OUTPUT INSERTED.* VALUES(1,'TOLE', 10)
WHEN NOT MATCHED BY SOURCE
DELETE CANDY

OUTPUT clause can also be used with the MERGE clause to send the inserted, deleted data to either table variable, temporary table or to an application.

A Quick glance at Output Clause

Advertisements