OUTPUT Clause for the MERGE Statements

MERGE ....

USING...

WHEN ...

WHEN ...

...

OUTPUT

   $action,

   inserted.*,

   deleted.*;

Merge with two options for MATCHED case

Remainder for MERGE syntax:

http://copypastenet.blogspot.co.il/2011/03/synchronize-two-tables-using-merge.html

If there are 2 options what to do when we have a MATCHED between the source and he target tables - most of the times it will be deletion and update - we can do that like this:

MERGE <target_table> [AS TARGET]

.....

WHEN MATCHED AND <condition>

THEN DELETE

WHEN MATCHED  -- all the other cases of MATCHED other then the condition above

THEN UPDATE 

SET ColumnA = s.SomeValue

.....

Synchronize two tables using Merge command

Merge - performs insert, update, or delete operations on a target table based on the results of a join with a source table.

MERGE <target_table> [AS TARGET]

USING <table_source> [AS SOURCE]
ON <search_condition>
[WHEN MATCHED –exists in the 2 tables (update)
THEN <merge_matched> ]
[WHEN NOT MATCHED [BY TARGET] –not exists in the target table (insert)
THEN <merge_not_matched> ]
[WHEN NOT MATCHED BY SOURCE –not exists in the source table (delete)

THEN <merge_ matched> ];

http://technet.microsoft.com/en-us/library/bb510625.aspx

http://www.mssqltips.com/tip.asp?tip=1704

http://gerireshef.wordpress.com/2010/06/07/%D7%A9%D7%99%D7%9E%D7%95%D7%A9-%D7%91%D7%A4%D7%A7%D7%95%D7%93%D7%AA-merge-%D7%9C%D7%A2%D7%93%D7%9B%D7%95%D7%9F-%D7%98%D7%91%D7%9C%D7%AA-slowly-changing-dimension/?blogsub=confirming#subscribe-blog