Microsoft SQL Server MERGE

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

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.



Got any Microsoft SQL Server Question?