SQL Server 2008 MERGE Statement

SQL Server 2008 introduces the MERGE statement which will allow users to perform insert, update and delete operations in a single statement. In the earlier versions of SQL Server to achieve the same functionality the database developer or database administrator needed to write separate statements to perform the insert, update or delete of data in one table based on certain conditions in another table.

Logic Used Prior to the Introduction of MERGE SQL Statement in SQL Server 2008
The database developer or database administrator prior to SQL Server 2008 needed to write lengthy Transact SQL code to achieve similar functionality. They first need to check if a record is available in the target table (destination table). In the target (destination) table if records are available based on the primary key value then the checks are performed to see is there any change in any of the data columns, if there are change in any of those columns then the record is UPDATE. In order to INSERT a new record in the target table (destination) you need to make sure that there is no such record present in target table. Finally the DELETE operation is performed when it’s identified that there is record available in target table (destination) and there is no similar record available in source table from where the comparison is done. Using MERGE statement which Microsoft has introduced with SQL Server 2008 database developers or DBA’s can achieve the same functionality by writing very less TSQL code. The code written using this logic will also have performance issue due to the complexity of joins etc. Even though this feature is introduced very late in SQL Server Product, going forward I am very sure that it will be adopted very quickly in many data warehouse projects.

How MERGE Statement Internally Works
The MERGE statement internally works as an individual insert, update and delete statement within a single Merge statement. You need to specify the SOURCE and the TARGET table or query which should be joined together. Within the MERGE statement you also need to specify the type of the data modification that needs to be performed when the records between the source and target are matched and what actions needs to be performed when they are not matched. With the introduction of MERGE statement the complex TSQL codes which was used earlier to do checks for the existence or inexistence of data within the data warehouse can be replaced with single Merge statement. The use of Merge statement will also improve the query performance.
Below are the three different matched clauses in MERGE:  

• WHEN MATCHED THEN
  • Rows that meet the criteria
• WHEN [TARGET] NOT MATCHED THEN
  • Rows that do not match with another row in the target table
• WHEN SOURCE NOT MATCHED THEN
  • Rows that do not match with another row in the source table

Overview on OUTPUT Clause
You can use the OUTPUT clause which was introduced in SQL Server 2005 to find out what records are being inserted, updated or deleted when you run a MERGE statement. OUTPUT clause will output information you specify for every record which is inserted, updated or deleted. The OUTPUT clause can also be useful to the value of the identity or any computed columns after an insert or update operation is performed against records in a table.  In the below example you can see that you are using OUTPUT clause to identify what actions has been taken on records.

Syntax of MERGE statement:

MERGE
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ] target_table [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias]
USING <table_source>
ON <merge_search_condition>
[ WHEN MATCHED [ AND <clause_search_condition> ]
THEN <merge_matched> ]
[ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
THEN <merge_not_matched> ]
[ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
THEN <merge_matched> ]
[ <output_clause> ]
[ OPTION ( <query_hint> [ ,...n ] ) ];