SQL Server: MERGE Statement

The MERGE statement allows DBA/Developer to combine the inserts, deletes, and updates, and to use a single statement to perform multiple DML actions. Using a MERGE statement instead of issuing multiple DML statements can improve performance. In a MERGE statement, DBA/Developer specifies a source and a target and includes a join. Then, DBA/Developer uses the MATCHED clauses to specify the actions to be performed. The basic syntax of the MERGE statement is as follows:

MERGE [INTO] target_table

USING source_table
ON join_condition [WHEN MATCHED THEN matched_action]
[WHEN NOT MATCHED [BY TARGET] THEN notmatched_action]
[WHEN NOT MATCHED BY SOURCE
THEN notmatchedsource_action];

The WHEN NOT MATCHED THEN clause specifies the action to take if the records from the source table are not in the target table. The WHEN MATCHED THEN clause specifies the action to take if the records from the source table are in the target table. In this scenario, DBA/Developer specified an UPDATE statement in the WHEN MATCHED THEN clause. Therefore, if a row in the ProductStaging table has the same ProductID as a row in the ProductDetails table, the row will be updated with new values for the ModifiedDate, Color, Style, and ProductLine columns. In this scenario, DBA/Developer also included an INSERT statement in the WHEN NOT MATCHED THEN clause. If a row in the ProductStaging table does not have a ProductID that matches a row in the ProductDetails table, the row will be inserted with the current date as the ModifiedDate column value and a ReorderPoint value of 0.In this scenario, DBA/Developer also included an OUTPUT clause. The OUTPUT clause allows DBA/Developer to retrieve and display information about the rows that were affected by the MERGE statement. The OUTPUT clause can display this information to the user, insert the data into another permanent or temporary table or table variable using an INTO clause, or pass the data to a nested DML statement for processing. Within the OUTPUT clause, DBA/Developer would specify the column values that should be retrieved by using the column names with the INSERTED and DELETED prefixes. The DELETED prefix returns the column value before the DML operation, and the INSERTED prefix returns the column value after the DML operation, but before executing any triggers. DBA/Developer can also use $action to return a string indicating which type of DML operation affected the row.

Further Reading:
https://www.simple-talk.com/sql/learn-sql-server/the-merge-statement-in-sql-server-2008/
http://blog.sqlauthority.com/2008/08/28/sql-server-2008-introduction-to-merge-statement-one-statement-for-insert-update-delete/
http://sqlmag.com/sql-server/merge-statement-tips

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