MERGE문

테스트 환경

 - OS : Windows 2008 R2 DataCenter

 - DBMS : SQL Server 2012 Enterprise Edition + SP4

 - 문서 : https://docs.microsoft.com/ko-kr/previous-versions/sql/sql-server-2012/bb510625(v=sql.110)

 

내용

 

개요

MERGE 문은 원본 테이블과의 조인 결과를 기반으로 대상 테이블에서 MATCH 조건에 따라 INSERT, UPDATE, DELETE 를 수행합니다.

 

 

구문

[ WITH [,...n] ] MERGE [ TOP ( expression ) [ PERCENT ] ] [ INTO ] [ WITH ( ) ] [ [ AS ] table_alias ] USING ON [ WHEN MATCHED [ AND ] THEN ] [ ...n ] [ WHEN NOT MATCHED [ BY TARGET ] [ AND ] THEN ] [ WHEN NOT MATCHED BY SOURCE [ AND ] THEN ] [ ...n ] [ ] [ OPTION ( [ ,...n ] ) ] ; ::= { [ database_name . schema_name . | schema_name . ] target_table } ::= { { [ [ ,...n ] ] [ [ , ] INDEX ( index_val [ ,...n ] ) ] } } ::= { table_or_view_name [ [ AS ] table_alias ] [ ] [ WITH ( table_hint [ [ , ]...n ] ) ] | rowset_function [ [ AS ] table_alias ] [ ( bulk_column_alias [ ,...n ] ) ] | user_defined_function [ [ AS ] table_alias ] | OPENXML | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ] | | | } ::= ::= { UPDATE SET | DELETE } ::= 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 ] ::= { INSERT [ ( column_list ) ] { VALUES ( values_list ) | DEFAULT VALUES } } ::= ::= { [ NOT ] | ( ) } [ { AND | OR } [ NOT ] { | ( ) } ] [ ,...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 INTO { @table_variable | output_table } [ (column_list) ] ] [ OUTPUT ] } ::= { | scalar_expression } [ [AS] column_alias_identifier ] [ ,...n ] ::= { DELETED | INSERTED | from_table_name } . { * | column_name } | $action