Microsoft SQL Server MERGE
Introduction
Starting with SQL Server 2008, it is possible to perform insert, update, or delete operations in a single statement using the MERGE statement.
The MERGE statement allows you to join a data source with a target table or view, and then perform multiple actions against the target based on the results of that join.
Syntax
- As per MSDN - https://msdn.microsoft.com/en-us/library/bb510625.aspx
[ WITH <common_table_expression> [,...n] ] 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> ]
[ ...n ] [ 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> ] [ ...n ] [ <output_clause> ] [ OPTION (
<query_hint> [ ,...n ] ) ] ; <target_table> ::= { [ database_name .
schema_name . | schema_name . ] target_table } <merge_hint>::= { { [
<table_hint_limited> [ ,...n ] ] [ [ , ] INDEX ( index_val [ ,...n ]
) ] } } <table_source> ::= { table_or_view_name [ [ AS ] table_alias
] [ <tablesample_clause> ] [ WITH ( table_hint [ [ , ]...n ] ) ] |
rowset_function [ [ AS ] table_alias ] [ ( bulk_column_alias [ ,...n
] ) ] | user_defined_function [ [ AS ] table_alias ] | OPENXML
<openxml_clause> | derived_table [ AS ] table_alias [ ( column_alias
[ ,...n ] ) ] | <joined_table> | <pivoted_table> | <unpivoted_table>
} <merge_search_condition> ::= <search_condition> <merge_matched>::=
{ UPDATE SET <set_clause> | DELETE } <set_clause>::= SET {
column_name = { expression | DEFAULT | NULL } | { udt_column_name.{ {
property_name = expression | field_name = expression } | method_name
( argument [ ,...n ] ) } } | column_name { .WRITE ( expression ,
@Offset , @Length ) } | @variable = expression | @variable = column =
expression | column_name { += | -= | *= | /= | %= | &= | ^= | |= }
expression | @variable { += | -= | *= | /= | %= | &= | ^= | |= }
expression | @variable = column { += | -= | *= | /= | %= | &= | ^= |
|= } expression } [ ,...n ] <merge_not_matched>::= { INSERT [ (
column_list ) ] { VALUES ( values_list ) | DEFAULT VALUES } }
<clause_search_condition> ::= <search_condition> ::= { [ NOT ] | (
<search_condition> ) } [ { AND | OR } [ NOT ] { | (
<search_condition> ) } ] [ ,...n ] ::= { expression { = | < > | ! = |
| > = | ! > | < | < = | ! < } expression | string_expression [ NOT ] LIKE string_expression [ ESCAPE 'escape_character' ] | expression [ NOT ] BETWEEN expression AND expression | expression IS [ NOT ] NULL | CONTAINS ( { column | * } , '< contains_search_condition >' ) | FREETEXT ( { column | * } , 'freetext_string' ) | expression [ NOT ] IN ( subquery | expression [ ,...n ] ) | expression { = | < > | ! = | | > = | ! > | < | < = | ! < } { ALL | SOME | ANY} ( subquery ) | EXISTS ( subquery ) } <output_clause>::= { [ OUTPUT <dml_select_list> INTO { @table_variable | output_table } [ (column_list) ] ] [ OUTPUT <dml_select_list> ] } <dml_select_list>::= { <column_name> | scalar_expression } [ [AS] column_alias_identifier ] [ ,...n ] <column_name> ::= { DELETED | INSERTED | from_table_name } . { * | column_name } | $action
Remarks
Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.
