[SQL] Merge information

With the Merge statement, the ability to insert a row if it does not exist or to update if it exists, is provided within a single statement.

The MERGE command is new to SQL Server 2008.

http://msdn.microsoft.com/en-us/library/bb522522.aspx

http://www.sqlservercurry.com/2008/05/sql-server-2008-merge-statement.html

http://blog.sqlauthority.com/2008/08/28/sql-server-2008-introduction-to-merge-statement-one-statement-for-insert-update-delete/

The $Action is a MERGE function that tells us what part of the MERGE each row came from.
Note that the OUTPUT can draw from both the source and the master.

The big advantage of the MERGE statement is being able to handle multiple actions in a single pass of the data sets, rather than requiring multiple passes with separate inserts and updates.
A well tuned optimizer could handle this extremely efficiently.


Example MERGE syntax can be found here:

Kimball PDF link for using MERGE for SCD