MERGE(Transact-SQL)

아티클
11/14/2023

적용 대상: SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance Azure Synapse Analytics

MERGE 문은 원본 테이블과의 조인 결과에서 대상 테이블에 대한 삽입, 업데이트 또는 삭제 작업을 실행합니다. 예를 들어 원본 테이블과의 차이점에 따라 대상 테이블에서 행을 삽입, 업데이트 및 삭제하여 두 테이블을 동기화합니다.

참고 항목

Azure Synapse Analytics와 관련된 MERGE 정보의 경우 버전 선택을 Azure Synapse Analytics로 변경합니다.

참고 항목

MERGE는 이제 10.0.17829.0 이상 버전의 Synapse 전용 SQL 풀에서 일반 공급됩니다. 전용 SQL 풀(이전 SQL DW)에 연결하고 SELECT @@VERSION을 실행합니다. 인스턴스가 최신 버전을 가져오도록 하려면 일시 중지 및 다시 시작이 필요할 수 있습니다.

팁

MERGE 문에 대해 설명된 조건부 동작은 두 테이블에 일치하는 특성이 복합적으로 혼합되어 있는 경우 가장 효과적입니다. 예를 들어, 행이 없는 경우 행을 삽입하고 행이 일치하지 않는 경우 행을 업데이트합니다. 다른 테이블의 행을 기반으로 한 테이블을 단순히 업데이트하는 경우 INSERT, UPDATE 및 DELETE 문을 사용하여 성능 및 확장성을 향상시킵니다. 예시:

INSERT tbl_A (col, col2)
SELECT col, col2
FROM tbl_B
WHERE NOT EXISTS (SELECT col FROM tbl_A A2 WHERE A2.col = tbl_B.col);

Transact-SQL 구문 표기 규칙

구문

SQL Server 및 Azure SQL Database에 대한 구문:

[ WITH <common_table_expression> [,...n] ]
MERGE
    [ TOP ( expression ) [ PERCENT ] ]
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]
    USING <table_source> [ [ AS ] table_alias ]
    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 . ] [ [ AS ] target_table ]
    | @variable [ [ AS ] target_table ]
    | common_table_expression_name [ [ AS ] target_table ]
}

<merge_hint>::=
{
    { [ <table_hint_limited> [ ,...n ] ]
    [ [ , ] { INDEX ( index_val [ ,...n ] ) | INDEX = index_val }]
    }
}

<merge_search_condition> ::=
    <search_condition>

<merge_matched>::=
    { UPDATE SET <set_clause> | DELETE }

<merge_not_matched>::=
{
    INSERT [ ( column_list ) ]
        { VALUES ( values_list )
        | DEFAULT VALUES }
}

<clause_search_condition> ::=
    <search_condition>

Azure Synapse Analytics 구문:

[ WITH <common_table_expression> [,...n] ]
MERGE
    [ INTO ] <target_table> [ [ AS ] table_alias ]
    USING <table_source> [ [ AS ] table_alias ]
    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 ]
    [ OPTION ( <query_hint> [ ,...n ] ) ]
;  -- The semi-colon is required, or the query will return a syntax error.

<target_table> ::=
{
    [ database_name . schema_name . | schema_name . ]
  target_table
}

<merge_search_condition> ::=
    <search_condition>

<merge_matched>::=
    { UPDATE SET <set_clause> | DELETE }

<merge_not_matched>::=
{
    INSERT [ ( column_list ) ]
        VALUES ( values_list )
}

<clause_search_condition> ::=
    <search_condition>

인수

WITH <common_table_expression>

MERGE 문의 범위 내에 정의된 임시로 명명된 결과 집합 또는 뷰(공통 테이블 식)를 지정합니다. 결과 집합은 단순 쿼리에서 파생되며 MERGE 문에서 참조됩니다. 자세한 내용은 WITH common_table_expression (Transact-SQL)을 참조하세요.

TOP ( expression ) [ PERCENT ]

영향을 받는 행의 개수 또는 비율을 지정합니다. 식은 행의 수 또는 비율일 수 있습니다. TOP 식에서 참조하는 행은 어떠한 순서로도 정렬되지 않습니다. 자세한 내용은 TOP(Transact-SQL)을 참조하세요.

TOP 절은 전체 원본 테이블과 전체 대상 테이블이 조인되고 조인된 행 중 삽입, 업데이트 또는 삭제 동작에 적합하지 않은 행이 제거된 후에 적용됩니다. TOP 절은 조인된 행 수를 지정된 값으로 더 줄입니다. 이러한 작업(삽입, 업데이트 또는 삭제)은 정렬되지 않은 방식으로 나머지 조인된 행에 적용됩니다. 즉, 행은 WHEN 절에 정의된 동작에 순서 없이 분산됩니다. 예를 들어 TOP (10)을 지정하면 10개 행이 영향을 받습니다. 이러한 행 중 7개는 업데이트되고 3개는 삽입되거나 1개는 삭제되고 5개는 업데이트되고 4개는 삽입될 수 있습니다.

원본 테이블에 대한 필터가 없으면 MERGE 문은 원본 테이블에서 테이블 검색 또는 클러스터형 인덱스 검색뿐만 아니라 대상 테이블의 테이블 검색 또는 클러스터형 인덱스 검색을 수행할 수 있습니다. 따라서 큰 테이블을 수정하기 위해 TOP 절을 사용하여 여러 일괄 처리를 만들면 I/O 성능에 영향을 주는 경우도 있습니다. 이러한 시나리오에서 연속된 모든 일괄 처리는 새로운 행을 대상으로 해야 합니다.

database_name

target_table이 있는 데이터베이스의 이름입니다.

schema_name

target_table이 속해 있는 스키마의 이름입니다.

target_table

에 따라 <clause_search_condition>데이터 행이 <table_source> 일치하는 테이블 또는 뷰입니다. target_table은 MERGE 문의 WHEN 절에 지정된 삽입, 업데이트 또는 삭제 작업의 대상입니다.

target_table이 뷰일 경우 이에 대한 모든 동작은 뷰 업데이트 조건을 충족해야 합니다. 자세한 내용은 뷰를 통해 데이터 수정을 참조하세요.

target_table은 원격 테이블일 수 없습니다. target_table 정의된 규칙을 가질 수 없습니다. target_table 메모리 최적화 테이블일 수 없습니다.

힌트는 .로 <merge_hint>지정할 수 있습니다.

<merge_hint> 는 Azure Synapse Analytics에서 지원되지 않습니다.

[ AS ] table_alias

target_table의 테이블을 참조하기 위한 대체 이름입니다.

USING <table_source>

에 따라 target_table 데이터 행과 일치하는 데이터 원본을 지정합니다.<merge_search_condition> 이 결과는 MERGE 문의 WHEN 절에서 수행할 동작을 나타냅니다. <table_source> 는 원격 테이블 또는 원격 테이블에 액세스하는 파생 테이블일 수 있습니다.

<table_source> 는 Transact-SQL 테이블 값 생성자를 사용하여 여러 행을 지정하여 테이블을 생성하는 파생 테이블일 수 있습니다.

<table_source>는 SELECT ... UNION ALL을 사용해 여러 행을 지정하여 테이블을 생성하는 파생 테이블일 수 있습니다.

[ AS ] table_alias

table_source의 테이블을 참조하기 위한 대체 이름입니다.

이 절의 구문 및 인수에 대한 자세한 내용은 FROM(Transact-SQL)을 참조하세요.

ON <merge_search_condition>

일치하는 부분을 확인하기 위해 <table_source>와 target_table을 조인하는 조건을 지정합니다.

주의

일치 용도로 사용되는 대상 테이블에서는 열만 지정하는 것이 중요합니다. 즉, 대상 테이블에서 원본 테이블의 해당 열과 비교할 열을 지정해야 합니다. 예를 들어 AND NOT target_table.column_x = value를 지정하는 것과 같이 ON 절에서 대상 테이블의 행을 필터링하여 쿼리 성능을 향상하려고 하면 안 됩니다. 이렇게 하면 예기치 않은 잘못된 결과가 반환됩니다.

WHEN MATCHED THEN <merge_matched>

ON<merge_search_condition>에서 반환 <table_source> 한 행과 일치하고 추가 검색 조건을 충족하는 *target_table 모든 행이 절에 <merge_matched> 따라 업데이트되거나 삭제되도록 지정합니다.

MERGE 문에는 최대 두 개의 WHEN MATCHED 절이 포함될 수 있습니다. 두 절을 지정하는 경우 첫 번째 절에는 AND <search_condition> 절이 함께 있어야 합니다. 지정된 행에 대해 두 번째 WHEN MATCHED 절은 첫 번째 WHEN MATCHED 절이 적용되지 않은 경우에만 적용됩니다. WHEN MATCHED 절이 두 개 있는 경우 하나는 UPDATE 동작을 지정해야 하고 다른 하나는 DELETE 동작을 지정해야 합니다. UPDATE가 절에 <merge_matched> 지정되고 둘 이상의 행 <table_source> 이 target_table 행과 <merge_search_condition>일치하는 경우 SQL Server는 오류를 반환합니다. MERGE 문은 동일한 행을 여러 번 업데이트하거나 업데이트하고 삭제할 수 없습니다.

WHEN NOT MATCHED [ BY TARGET ] THEN <merge_not_matched>

TARGET_TABLE 행과 일치하지 않지만 추가 검색 조건이 있는 경우 ON <merge_search_condition> 에서 반환하는 <table_source> 모든 행에 대해 행이 target_table 삽입 되도록 지정합니다. 삽입할 값은 절에 <merge_not_matched> 의해 지정됩니다. MERGE 문에는 WHEN NOT MATCHED [ BY TARGET ] 절이 하나만 포함될 수 있습니다.

WHEN NOT MATCHED BY SOURCE THEN <merge_matched>

ON<merge_search_condition>에서 반환 <table_source> 한 행과 일치하지 않고 추가 검색 조건을 충족하는 *target_table 모든 행이 절에 <merge_matched> 따라 업데이트되거나 삭제되도록 지정합니다.

MERGE 문에는 최대 두 개의 WHEN NOT MATCHED BY SOURCE 절이 포함될 수 있습니다. 두 절을 지정하는 경우 첫 번째 절에는 AND <clause_search_condition> 절이 함께 있어야 합니다. 지정된 행에 대해 두 번째 WHEN NOT MATCHED BY SOURCE 절은 첫 번째 WHEN MATCHED 절이 적용되지 않은 경우에만 적용됩니다. WHEN NOT MATCHED BY SOURCE 절이 두 개 있는 경우 하나는 UPDATE 동작을 지정해야 하고 다른 하나는 DELETE 동작을 지정해야 합니다. 대상 테이블의 열만 .에서 <clause_search_condition>참조할 수 있습니다.

행이 반환 <table_source>되지 않으면 원본 테이블의 열에 액세스할 수 없습니다. 절에 <merge_matched> 지정된 업데이트 또는 삭제 작업이 원본 테이블의 열을 참조하는 경우 오류 207(잘못된 열 이름)이 반환됩니다. 예를 들어 이 절 WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1 은 원본 테이블에 액세스할 수 없으므로 문이 실패 Col1 할 수 있습니다.

AND <clause_search_condition>

유효한 검색 조건을 지정합니다. 자세한 내용은 검색 조건(Transact-SQL)을 참조하세요.

<table_hint_limited>

MERGE 문이 수행하는 각 삽입, 업데이트 또는 삭제 동작의 대상 테이블에 적용되는 하나 이상의 테이블 힌트를 지정합니다. WITH 키워드와 괄호가 필요합니다.

NOLOCK 및 READUNCOMMITTED는 허용되지 않습니다. 테이블 힌트에 대한 자세한 내용은 테이블 힌트(Transact-SQL)를 참조하세요.

INSERT 문의 대상 테이블에 TABLOCK 힌트를 지정하는 것은 TABLOCKX 힌트를 지정하는 것과 결과가 같습니다. 두 경우 모두 테이블이 배타적으로 잠깁니다. FORCESEEK를 지정할 경우 원본 테이블과 조인된 대상 테이블의 암시적 인스턴스에 이 힌트가 적용됩니다.

주의

WHEN NOT MATCHED [ BY TARGET ] THEN INSERT를 사용하여 READPAST를 지정하면 UNIQUE 제약 조건을 위반하는 INSERT 작업이 발생할 수 있습니다.

INDEX ( index_val [ ,...n ] )

원본 테이블과의 암시적 조인을 수행하는 대상 테이블에 있는 하나 이상의 인덱스에 대한 이름 또는 ID를 지정합니다. 자세한 내용은 테이블 힌트(Transact-SQL)를 참조하세요.

<output_clause>

업데이트, 삽입 또는 삭제되는 target_table의 모든 행과 일치하는 행을 임의의 순서로 반환합니다. $action 은 출력 절에 지정할 수 있습니다. $action는 각 행에 대해 수행된 작업에 따라 각 행UPDATEINSERTDELETE에 대한 세 가지 값 중 하나를 반환하는 nvarchar(10) 형식의 열입니다. OUTPUT 절은 MERGE의 영향을 받는 행을 쿼리하거나 계산하는 데 권장되는 방법입니다. 이 절의 인수 및 동작에 대한 자세한 내용은 OUTPUT 절(Transact-SQL)을 참조하세요.

OPTION ( <query_hint> [ ,...n ] )

데이터베이스 엔진이 문을 처리하는 방식을 사용자 지정하기 위한 최적화 프로그램 힌트를 지정합니다. 자세한 내용은 힌트(Transact-SQL) - 쿼리를 참조 하세요.

<merge_matched>

ON에서 반환한 행 과 일치하지 않고 추가 검색 조건을 충족하는 모든 target_table 행에 <table_source> <merge_search_condition>적용되는 업데이트 또는 삭제 작업을 지정합니다.

UPDATE SET <set_clause>

대상 테이블에서 업데이트할 열 또는 변수 이름의 목록 및 이 목록을 업데이트하는 데 사용할 값을 지정합니다.

이 절의 인수에 대한 자세한 내용은 UPDATE(Transact-SQL)를 참조하세요. 변수를 열과 동일한 값으로 설정할 수는 없습니다.

Delete

target_table의 행과 일치하는 행이 삭제되도록 지정합니다.

<merge_not_matched>

대상 테이블에 삽입할 값을 지정합니다.

( column_list )

데이터를 삽입할 대상 테이블에 있는 하나 이상의 열 목록입니다. 열은 단일 부분으로 지정해야 합니다. 그렇지 않으면 MERGE 문이 실패합니다. column_list는 괄호로 묶고 쉼표로 구분해야 합니다.

VALUES ( values_list)

대상 테이블에 삽입할 값을 반환하는 상수, 변수 또는 식의 쉼표로 구분된 목록입니다. 식에는 EXECUTE 문이 포함될 수 없습니다.

DEFAULT VALUES

삽입된 행에 각 열에 대해 정의된 기본값이 포함되도록 합니다.

이 절에 대한 자세한 내용은 INSERT(Transact-SQL)를 참조하세요.

<search_condition>

지정 <merge_search_condition> 하거나 <clause_search_condition>지정할 검색 조건을 지정합니다. 이 절의 인수에 대한 자세한 내용은 검색 조건(Transact-SQL)을 참조하세요.

<graph search pattern>

그래프 일치 패턴을 지정합니다. 이 절의 인수에 대한 자세한 내용은 MATCH(Transact-SQL)를 참조하세요.

설명

세 개의 MATCHED 절 중 최소한 하나를 지정해야 하지만 임의의 순서로 지정할 수 있습니다. 변수는 동일한 MATCHED 절에서 두 번 이상 업데이트할 수 없습니다.

MERGE 문에 의해 대상 테이블에 지정된 삽입, 업데이트 또는 삭제 동작은 연계 참조 무결성 제약 조건을 포함하여 해당 테이블에 정의된 제약 조건의 제한을 받습니다. IGNORE_DUP_KEY가 대상 테이블의 고유 인덱스에 대해 ON인 경우 MERGE는 이 설정을 무시합니다.

MERGE 문에는 문 종결자로 세미콜론(;)이 필요합니다. MERGE 문이 종결자 없이 실행되면 오류 10713이 발생합니다.

MERGE 후에 @@ROWCOUNT(Transact-SQL)을 사용하면 삽입, 업데이트 및 삭제된 행의 총 개수가 클라이언트에 반환됩니다.

MERGE는 데이터베이스 호환성 수준이 이상으로 설정된 100 경우 완전히 예약된 키워드입니다. MERGE 문은 데이터베이스 호환성 수준과 100 데이터베이스 호환성 수준에서 모두 90 사용할 수 있지만 데이터베이스 호환성 수준이 설정된 경우 키워드가 완전히 예약되지는 90않습니다.

주의

지연 업데이트 복제를 사용하는 경우 MERGE 문을 사용하지 마세요. MERGE 및 지연된 업데이트 트리거는 호환되지 않습니다. MERGE 문을 insert 또는 update 문으로 바꾸세요.

Azure Synapse Analytics 고려 사항

Azure Synapse Analytics에서 MERGE 명령은 SQL Server 및 Azure SQL 데이터베이스에 비해 다음과 같은 차이점이 있습니다.

MERGE를 사용하여 배포 키 열을 업데이트하는 것은 10.0.17829.0보다 오래된 빌드에서 지원되지 않습니다. 일시 중지하거나 강제로 업그레이드할 수 없는 경우 버전 10.0.17829.0까지 ANSI UPDATE FROM ... JOIN 문을 해결 방법으로 사용합니다.
MERGE 업데이트는 삭제 및 삽입 쌍으로 구현됩니다. MERGE 업데이트의 영향을 받는 행 수에는 삭제된 행과 삽입된 행이 포함됩니다.
MERGE...WHEN NOT MATCHED INSERT 는 IDENTITY 열이 있는 테이블에 대해 지원되지 않습니다.
원본 테이블의 USING 절에는 테이블 값 생성자를 사용할 수 없습니다. SELECT ... UNION ALL을 사용하여 여러 행이 있는 파생 원본 테이블을 만듭니다.
다음 표에서는 배포 유형이 다른 테이블에 대한 지원을 설명합니다.

Azure Synapse Analytics의 MERGE CLAUSE	지원되는 대상 배포 테이블	지원되는 원본 배포 테이블	의견
`WHEN MATCHED`	모든 배포 유형	모든 배포 유형
`NOT MATCHED BY TARGET`	HASH	모든 배포 유형	두 테이블을 동기화하는 데 사용합니다 `UPDATE`/`DELETE FROM...JOIN` .
`NOT MATCHED BY SOURCE`	모든 배포 유형	모든 배포 유형

팁

MERGE에서 배포 해시 키를 JOIN 열로 사용하고 같음 비교만 수행하는 경우 중복 업데이트이므로 WHEN MATCHED THEN UPDATE SET의 열 목록에서 배포 키를 생략할 수 있습니다.

Azure Synapse Analytics에서 10.0.17829.0 이전 빌드의 MERGE 명령은 특정 조건에서 대상 테이블을 일관되지 않은 상태로 두고 행이 잘못된 배포에 배치되어 나중에 쿼리가 잘못된 결과를 반환하도록 할 수 있습니다. 이 문제는 다음과 같은 두 가지 경우에 발생할 수 있습니다.

시나리오 의견

케이스 1
보조 인덱스 또는 UNIQUE 제약 조건을 포함하는 HASH 분산 TARGET 테이블에서 MERGE를 사용합니다. - Synapse SQL 10.0.15563.0 이상 버전에서 수정되었습니다.
- 10.0.15563.0보다 낮은 버전을 반환하는 경우 SELECT @@VERSION 수동으로 Synapse SQL 풀을 일시 중지하고 다시 시작하여 이 수정 사항을 선택합니다.
- 수정 사항이 Synapse SQL 풀에 적용될 때까지 보조 인덱스 또는 UNIQUE 제약 조건이 있는 HASH 분산 TARGET 테이블에는 MERGE 명령을 사용하지 마세요.

케이스 2
MERGE를 사용하여 문이 HASH 분산 테이블의 배포 키 열을 업데이트합니다. - Synapse SQL 10.0.17829.0 이상 버전에서 수정되었습니다.
- 10.0.17829.0보다 낮은 버전을 반환하는 경우 SELECT @@VERSION 수동으로 Synapse SQL 풀을 일시 중지하고 다시 시작하여 이 수정 사항을 선택합니다.
- 수정이 Synapse SQL 풀에 적용될 때까지 배포 키 열을 업데이트하는 데 MERGE 명령을 사용하지 마세요.

시나리오	의견
케이스 1 보조 인덱스 또는 UNIQUE 제약 조건을 포함하는 HASH 분산 TARGET 테이블에서 MERGE를 사용합니다.	- Synapse SQL 10.0.15563.0 이상 버전에서 수정되었습니다. - 10.0.15563.0보다 낮은 버전을 반환하는 경우 `SELECT @@VERSION` 수동으로 Synapse SQL 풀을 일시 중지하고 다시 시작하여 이 수정 사항을 선택합니다. - 수정 사항이 Synapse SQL 풀에 적용될 때까지 보조 인덱스 또는 UNIQUE 제약 조건이 있는 HASH 분산 TARGET 테이블에는 MERGE 명령을 사용하지 마세요.
케이스 2 MERGE를 사용하여 문이 HASH 분산 테이블의 배포 키 열을 업데이트합니다.	- Synapse SQL 10.0.17829.0 이상 버전에서 수정되었습니다. - 10.0.17829.0보다 낮은 버전을 반환하는 경우 `SELECT @@VERSION` 수동으로 Synapse SQL 풀을 일시 중지하고 다시 시작하여 이 수정 사항을 선택합니다. - 수정이 Synapse SQL 풀에 적용될 때까지 배포 키 열을 업데이트하는 데 MERGE 명령을 사용하지 마세요.

두 시나리오의 업데이트는 이전 MERGE 실행의 영향을 받은 테이블을 복구하지 않습니다. 다음 스크립트를 사용하여 영향을 받는 테이블을 수동으로 식별하고 복구합니다.

데이터베이스의 HASH 분산 테이블이 문제가 될 수 있는지 확인하려면(앞에서 언급한 경우에 사용된 경우) 다음 문을 실행합니다.

-- Case 1
SELECT a.name,
    c.distribution_policy_desc,
    b.type
FROM sys.tables a
INNER JOIN sys.indexes b
    ON a.object_id = b.object_id
INNER JOIN sys.pdw_table_distribution_properties c
    ON a.object_id = c.object_id
WHERE b.type = 2
    AND c.distribution_policy_desc = 'HASH';

-- Subject to Case 2, if distribution key value is updated in MERGE statement
SELECT a.name,
    c.distribution_policy_desc
FROM sys.tables a
INNER JOIN sys.pdw_table_distribution_properties c
    ON a.object_id = c.object_id
WHERE c.distribution_policy_desc = 'HASH';

MERGE에 대한 HASH 분산 테이블이 사례 1 또는 사례 2의 영향을 받는지 확인하려면 다음 단계에 따라 테이블에 행이 잘못된 분포로 배치되었는지 검사합니다. 반환되는 경우 no need for repair 이 테이블은 영향을 받지 않습니다.

IF object_id('[check_table_1]', 'U') IS NOT NULL
    DROP TABLE [check_table_1]
GO

IF object_id('[check_table_2]', 'U') IS NOT NULL
    DROP TABLE [check_table_2]
GO

CREATE TABLE [check_table_1]
    WITH (DISTRIBUTION = ROUND_ROBIN) AS

SELECT <DISTRIBUTION_COLUMN> AS x
FROM <MERGE_TABLE>
GROUP BY <DISTRIBUTION_COLUMN>;
GO

CREATE TABLE [check_table_2]
    WITH (DISTRIBUTION = HASH (x)) AS

SELECT x
FROM [check_table_1];
GO

IF NOT EXISTS (
        SELECT TOP 1 *
        FROM (
            SELECT <DISTRIBUTION_COLUMN> AS x
            FROM <MERGE_TABLE>

            EXCEPT

            SELECT x
            FROM [check_table_2]
            ) AS tmp
        )
    SELECT 'no need for repair' AS result
ELSE
    SELECT 'needs repair' AS result
GO

IF object_id('[check_table_1]', 'U') IS NOT NULL
    DROP TABLE [check_table_1]
GO

IF object_id('[check_table_2]', 'U') IS NOT NULL
    DROP TABLE [check_table_2]
GO

영향을 받는 테이블을 복구하려면 이 문을 실행하여 이전 테이블의 모든 행을 새 테이블로 복사해야 합니다.

IF object_id('[repair_table_temp]', 'U') IS NOT NULL
    DROP TABLE [repair_table_temp];
GO

IF object_id('[repair_table]', 'U') IS NOT NULL
    DROP TABLE [repair_table];
GO

CREATE TABLE [repair_table_temp]
    WITH (DISTRIBUTION = ROUND_ROBIN) AS

SELECT *
FROM <MERGE_TABLE>;
GO

-- [repair_table] will hold the repaired table generated from <MERGE_TABLE>
CREATE TABLE [repair_table]
    WITH (DISTRIBUTION = HASH (<DISTRIBUTION_COLUMN>)) AS

SELECT *
FROM [repair_table_temp];
GO

IF object_id('[repair_table_temp]', 'U') IS NOT NULL
    DROP TABLE [repair_table_temp];
GO

문제 해결

특정 시나리오에서 MERGE 문은 대상 또는 원본 테이블에 열이 1,024개 없는 경우에도 오류가 CREATE TABLE failed because column <> in table <> exceeds the maximum of 1024 columns.발생할 수 있습니다. 이 시나리오는 다음 조건 중 어느 것이라도 충족될 때 발생할 수 있습니다.

MERGE 내의 DELETE, UPDATE SET 또는 INSERT 작업에서 여러 열이 지정됩니다(WHEN [NOT] MATCHED 절과는 관련이 없음).
JOIN 조건의 모든 열에 NCI(비클러스터형 인덱스)가 있습니다.
대상 테이블이 HASH 분산됨

이 오류가 발견되면 제안된 해결 방법은 다음과 같습니다.

JOIN 열에서 NCI(비클러스터형 인덱스)를 제거하거나 NCI가 없는 열에 조인합니다. 나중에 JOIN 열에 NCI를 포함하도록 기본 테이블을 업데이트하는 경우 MERGE 문은 런타임 시 이 오류에 취약할 수 있습니다. 자세한 내용은 DROP INDEX를 참조하세요.
MERGE 대신 UPDATE, DELETE 및 INSERT 문을 사용합니다.

트리거 구현

MERGE 문에 지정된 모든 삽입, 업데이트 또는 삭제 동작에 대해 SQL Server는 대상 테이블에 정의된 해당 AFTER 트리거를 실행하지만 트리거를 첫 번째 또는 마지막으로 실행하는 동작을 지정하지는 않습니다. 동일한 동작에 대해 정의된 트리거는 사용자가 지정하는 순서대로 실행됩니다. 트리거 실행 순서를 설정하는 방법은 시작 및 끝 트리거 지정을 참조하세요.

대상 테이블에 MERGE 문에서 수행하는 삽입, 업데이트 또는 삭제 동작에 대해 정의된 INSTEAD OF 트리거가 활성화되어 있으면 MERGE 문에 지정된 모든 동작에 대해서도 INSTEAD OF 트리거가 활성화되어 있어야 합니다.

target_table에 INSTEAD OF UPDATE 또는 INSTEAD OF DELETE 트리거가 정의되어 있으면 업데이트 또는 삭제 작업이 실행되지 않습니다. 대신 트리거가 실행되고 이에 따라 inserted 및 deleted 테이블이 채워집니다.

target_table에 INSTEAD OF INSERT 트리거가 정의되어 있으면 삽입 작업이 수행되지 않습니다. 대신, 이에 따라 테이블이 채워집니다.

참고 항목

별도의 INSERT, UPDATE 및 DELETE 문과 달리 트리거 내의 @@ROWCOUNT 반영되는 행 수가 더 많을 수 있습니다. 트리거가 캡처하는 데이터 수정 문과 관계없이 AFTER 트리거 내의 @@ROWCOUNT는 MERGE의 영향을 받는 총 행 수를 반영합니다. 예를 들어 MERGE 문이 한 행을 삽입하고, 한 행을 업데이트하고, 한 행을 삭제하면 트리거가 INSERT 문에 대해서만 선언되더라도 @@ROWCOUNT는 모든 AFTER 트리거에 대해 3이 됩니다.

사용 권한

원본 테이블에 대해서는 SELECT 권한이 필요하고 대상 테이블에 대해서는 INSERT, UPDATE 또는 DELETE 권한이 필요합니다. 자세한 내용은 SELECT, INSERT, UPDATE 및 DELETE 문서의 권한 섹션을 참조하세요.

인덱스 모범 사례

MERGE 문을 사용하면 여러 개의 개별 DML 문을 단일 문으로 대체할 수 있습니다. 이렇게 하면 작업이 하나의 문 내에서 수행되므로 원본 및 대상 테이블의 데이터가 처리되는 횟수가 최소화되어 쿼리 성능이 향상됩니다. 단, 성능 향상을 위해서는 인덱스, 조인 및 기타 고려 사항이 올바르게 설정되어야 합니다.

MERGE 문의 성능을 높이려면 다음 인덱스 지침을 따르는 것이 좋습니다.

MERGE의 원본과 대상 간의 조인을 용이하게 하는 인덱스를 만듭니다.
- 대상 테이블에 대한 조인 논리를 포함하는 키가 있는 원본 테이블의 조인 열에 인덱스를 만듭니다. 가능하면 고유해야 합니다.
- 또한 대상 테이블의 조인 열에 인덱스를 만듭니다. 가능하면 고유한 클러스터형 인덱스여야 합니다.
- 이러한 두 인덱스는 테이블의 데이터가 정렬되도록 하고 고유성을 통해 비교 성능을 지원합니다. 쿼리 최적화 프로그램이 중복 행을 찾아 업데이트하기 위해 별도로 유효성 검사를 수행할 필요가 없고 추가 정렬 작업도 필요 없으므로 쿼리 성능이 개선됩니다.
columnstore 인덱스 형식의 테이블을 MERGE 문의 대상으로 사용하지 마세요. UPDATE와 마찬가지로 스테이징된 rowstore 테이블을 업데이트한 다음 UPDATE 또는 MERGE 대신 일괄 처리된 DELETE 및 INSERT를 수행하여 columnstore 인덱스의 성능이 향상될 수 있습니다.

MERGE의 동시성 고려 사항

잠금 측면에서 MERGE는 불연속, 연속 INSERT, UPDATE 및 DELETE 문과 다릅니다. MERGE는 INSERT, UPDATE 및 DELETE 작업을 실행하지만 다른 잠금 메커니즘을 사용합니다. 일부 애플리케이션 요구 사항에 대해 개별 INSERT, UPDATE 및 DELETE 문을 작성하는 것이 더 효율적일 수 있습니다. MERGE는 대규모로 복잡한 동시성 문제를 발생하거나 고급 문제 해결이 필요할 수 있습니다. 따라서 프로덕션에 배포하기 전에 MERGE 문을 철저히 테스트하도록 계획해야 합니다.

MERGE 문은 다음과 같은 시나리오(이에 국한되지 않음)에서 불연속 INSERT, UPDATE 및 DELETE 작업을 적절하게 대체합니다.

행 개수가 많은 ETL 작업으로 다른 동시 실행 작업이 필요하지 않은* 시간 동안 실행됩니다. 동시성이 많이 필요한 경우 별도의 INSERT, UPDATE 및 DELETE 논리가 MERGE 문보다 차단이 적어 더 나은 성능을 발휘할 수 있습니다.
행 개수 및 트랜잭션이 적은 복잡한 작업은 오랫동안 실행될 가능성이 작습니다.
최적의 실행 계획을 보장하도록 인덱스를 디자인할 수 있는 사용자 테이블을 포함하는 복잡한 작업으로 테이블 검색 및 조회를 방지하고 대신 인덱스 검색을 사용합니다.

기타 동시성 고려 사항은 다음과 같습니다.

MERGE로 고유 키를 삽입하고 업데이트해야 하는 일부 시나리오에서는 HOLDLOCK을 지정하여 고유 키 위반을 방지합니다. HOLDLOCK은 SERIALIZABLE 트랜잭션 격리 수준의 동의어이며, 다른 동시 트랜잭션에서 이 트랜잭션이 읽은 데이터를 수정할 수 없도록 합니다. SERIALIZABLE은 가장 안전한 격리 수준이지만 데이터 범위에 대한 잠금을 유지하여 읽기가 진행되는 동안 가상 행이 삽입되거나 업데이트되지 않도록 하는 최소 동시성을 다른 트랜잭션에 제공합니다. HOLDLOCK에 대한 자세한 내용은 힌트 및 SET TRANSACTION ISOLATION LEVEL(Transact-SQL)을 참조하세요.

JOIN 모범 사례

MERGE 문의 성능을 높이고 정확한 결과를 얻으려면 다음 조인 지침을 따르는 것이 좋습니다.

ON <merge_search_condition> 절에는 원본 및 대상 테이블의 데이터 비교를 위한 조건을 나타내는 검색 조건만 지정합니다. 즉, 대상 테이블에서 원본 테이블의 해당 열과 비교할 열만 지정해야 합니다.
상수와 같은 다른 값에 대한 비교는 포함하지 않습니다.

원본 또는 대상 테이블에서 행을 필터링하려면 다음 방법 중 하나를 사용합니다.

적절한 WHEN 절에 행 필터링을 위한 검색 조건을 지정합니다(예: 예를 들어 WHEN NOT MATCHED AND S.EmployeeName LIKE 'S%' THEN INSERT....
필터링된 행을 반환하는 원본 또는 대상에 대한 뷰를 정의하고 이 뷰를 원본 또는 대상 테이블로 참조합니다. 대상 테이블에 대해 정의된 뷰의 모든 동작은 뷰 업데이트를 위한 조건을 충족해야 합니다. 뷰를 사용하여 데이터를 업데이트하는 방법은 뷰를 통해 데이터 수정을 참조하세요.
WITH <common table expression> 절을 사용하여 원본 또는 대상 테이블에서 행을 필터링합니다. 이 메서드는 ON 절에 추가 검색 조건을 지정하는 것과 유사하며 잘못된 결과를 생성할 수 있습니다. 사용하지 않는 것이 좋으며 사용할 경우 구현 전에 철저히 테스트해야 합니다.

MERGE 문의 조인 작업은 SELECT 문의 조인과 동일한 방식으로 최적화됩니다. 즉, SQL Server에서 조인을 처리할 때 쿼리 최적화 프로그램은 여러 가지 가능한 방법 중 가장 효율적인 방법을 선택합니다. 원본 및 대상의 크기가 비슷하고 ‘인덱스를 위한 최선의 방법’ 섹션에 설명된 인덱스 지침을 원본 및 대상 테이블에 적용한 경우 병합 조인 연산자가 가장 효율적인 쿼리 계획입니다. 두 테이블 모두 한 번만 검색되고 데이터를 정렬할 필요가 없기 때문입니다. 원본 테이블이 대상 테이블보다 작은 경우 중첩 루프 연산자가 좋습니다.

MERGE 문에서 OPTION (<query_hint>) 절을 지정하여 특정 조인을 사용하도록 강제할 수 있습니다. 해시 조인은 인덱스를 사용하지 않으므로 MERGE 문에 대한 쿼리 힌트로는 사용하지 않는 것이 좋습니다.

매개 변수화 모범 사례

SELECT, INSERT, UPDATE 또는 DELETE 문이 매개 변수 없이 실행되는 경우 SQL Server 쿼리 최적화 프로그램에서 내부적으로 문을 매개 변수화하도록 선택할 수 있습니다. 즉, 쿼리에 포함된 모든 리터럴 값이 매개 변수로 대체됩니다. 예를 들어 문 INSERT dbo.MyTable (Col1, Col2) VALUES (1, 10)은 내부적으로 .로 INSERT dbo.MyTable (Col1, Col2) VALUES (@p1, @p2)구현될 수 있습니다. 단순 매개 변수화라고 하는 이 프로세스는 관계형 엔진이 새 SQL 문을 이전에 컴파일된 기존 실행 계획과 일치시키는 기능을 향상합니다. 쿼리 컴파일 및 다시 컴파일 빈도가 감소하므로 쿼리 성능이 향상될 수 있습니다. 쿼리 최적화 프로그램은 MERGE 문에 단순 매개 변수화 프로세스를 적용하지 않습니다. 따라서 MERGE 문이 실행될 때마다 새 계획이 컴파일되므로 리터럴 값이 포함된 MERGE 문이 수행되지 않을 수 있으며 개별 INSERT, UPDATE 또는 DELETE 문이 수행되지 않을 수 있습니다.

쿼리 성능을 높이려면 다음 매개 변수화 지침을 따르는 것이 좋습니다.

ON <merge_search_condition> 절과 MERGE 문의 WHEN 절에 있는 모든 리터럴 값을 매개 변수화합니다. 예를 들어 리터럴 값을 적절한 입력 매개 변수로 대체하여 MERGE 문을 저장 프로시저에 통합할 수 있습니다.
문을 매개 변수화할 수 없는 경우 TEMPLATE 형식의 계획 지침을 만들고 PARAMETERIZATION FORCED 쿼리 힌트를 이 계획 지침에 지정합니다. 자세한 내용은 계획 지침을 사용하여 쿼리 매개 변수화 동작 지정을 참조하세요.
MERGE 문이 데이터베이스에서 자주 실행되는 경우 데이터베이스의 PARAMETERIZATION 옵션을 FORCED로 설정하는 것이 좋습니다. 이 옵션을 설정할 때는 신중해야 합니다. PARAMETERIZATION 옵션은 데이터베이스 수준 설정이므로 데이터베이스에 대한 모든 쿼리의 처리 방식에 영향을 미칩니다. 자세한 내용은 강제 매개 변수화를 참조하세요.
계획 지침보다 새롭고 더 간단한 대안으로 쿼리 저장소 힌트를 사용한 유사한 전략을 사용하는 것이 좋습니다. 자세한 내용은 쿼리 저장소 힌트를 참조하세요.

TOP 절 모범 사례

MERGE 문에서 TOP 절은 원본 테이블과 대상 테이블이 조인되고 삽입, 업데이트 또는 삭제 동작에 적합하지 않은 행이 제거된 후에 영향을 받는 행의 개수나 비율을 지정합니다. TOP 절은 조인된 행 수를 지정된 값으로 더 줄이며, 삽입, 업데이트 또는 삭제 동작은 나머지 조인된 행에 순서 없이 적용됩니다. 즉, 행은 WHEN 절에 정의된 동작에 순서 없이 분산됩니다. 예를 들어 TOP(10)을 지정하면 10개 행에 영향을 줍니다. 이러한 행 중 7개는 업데이트되고 3개는 삽입되거나 1개는 삭제되고 5개는 업데이트되고 4개는 삽입될 수 있습니다.

일반적으로 TOP 절을 사용하여 큰 테이블에서 일괄 처리로 DML(데이터 조작 언어) 작업을 수행합니다. MERGE 문에서 TOP 절을 이러한 용도로 사용하는 경우 다음 내용을 알고 있어야 합니다.

I/O 성능이 영향을 받을 수 있습니다.

MERGE 문은 원본 테이블과 대상 테이블 모두에서 전체 테이블 검색을 수행합니다. 작업을 일괄 처리로 분할하면 일괄 처리당 수행되는 쓰기 작업의 수가 줄어듭니다. 그러나 각 일괄 처리는 원본 및 대상 테이블의 전체 테이블 검색을 수행합니다. 결과 읽기 작업은 테이블의 쿼리 및 기타 동시 작업의 성능에 영향을 줄 수 있습니다.
잘못된 결과가 발생할 수 있습니다.

연속된 모든 일괄 처리는 새로운 행을 대상으로 해야 합니다. 그렇지 않은 경우 대상 테이블에 중복 행을 잘못 삽입하는 등의 원하지 않는 동작이 발생할 수 있습니다. 이러한 현상은 대상 일괄 처리에는 없었지만 전체 대상 테이블에는 있었던 행이 원본 테이블에 포함되는 경우 발생할 수 있습니다. 정확한 결과를 보장하려면 다음과 같이 합니다.
- ON 절을 사용하여 기존 대상 행에 영향을 미치는 원본 행과 완전히 새로운 행을 확인합니다.
- WHEN MATCHED 절의 추가 조건을 사용하여 대상 행이 이전 일괄 처리에 의해 이미 업데이트되었는지 확인합니다.
- WHEN MATCHED 절과 SET 논리에서 추가 조건을 사용하여 동일한 행이 두 번 업데이트되지 않는지 확인합니다.

TOP 절은 이러한 절이 적용된 후에 적용되므로 문을 실행할 때마다 하나의 완전히 일치하지 않는 행이 삽입되거나 하나의 기존 행이 업데이트됩니다.

대량 로드 모범 사례

MERGE 문을 사용하여 OPENROWSET(BULK...) 절을 테이블 원본으로 지정하면 원본 데이터 파일의 데이터를 대상 테이블로 효율적으로 대량 로드할 수 있습니다. 이렇게 하면 전체 파일이 하나의 일괄 처리에서 처리됩니다.

대량 병합 프로세스의 성능을 높이려면 다음 지침을 따르는 것이 좋습니다.

대상 테이블의 조인 열에 클러스터형 인덱스를 만듭니다.
대량 로드 MERGE 기간 동안 대상 테이블에서 고유하지 않은 다른 비클러스터형 인덱스를 사용하지 않도록 설정하고 나중에 사용하도록 설정합니다. 이 작업은 일반적이며 야간 대량 데이터 작업에 유용합니다.
절의 ORDER 및 UNIQUE 힌트를 OPENROWSET(BULK...) 사용하여 원본 데이터 파일의 정렬 방법을 지정합니다.

기본적으로 대량 작업은 데이터 파일이 정렬되지 않았음을 전제로 합니다. 따라서 쿼리 최적화 프로그램이 보다 효율적인 쿼리 계획을 생성할 수 있도록 원본 데이터를 대상 테이블의 클러스터형 인덱스에 따라 정렬하고, ORDER 힌트를 사용하여 순서를 나타내야 합니다. 힌트는 런타임에 유효성이 검사됩니다. 데이터 스트림이 지정된 힌트를 따르지 않으면 오류가 발생합니다.

이러한 지침을 통해 조인 키의 고유성을 확보하고 원본 파일의 데이터 정렬 순서가 대상 테이블과 일치하도록 할 수 있습니다. 추가 정렬 작업이 필요 없고 불필요한 데이터 복사가 없으므로 쿼리 성능이 향상됩니다.

MERGE 성능 측정 및 진단

다음 기능을 사용하여 MERGE 문의 성능을 측정 및 진단할 수 있습니다.

sys.dm_exec_query_optimizer_info 동적 관리 뷰에서 merge stmt 카운터를 사용하여 MERGE 문에 대한 쿼리 최적화 수를 반환합니다.
sys.dm_exec_plan_attributes 동적 관리 뷰에서 merge_action_type 특성을 사용하여 MERGE 문의 결과로 사용되는 트리거 실행 계획의 유형을 반환합니다.
확장 이벤트 세션을 사용하여 다른 DML(데이터 조작 언어) 문에 대해 사용하는 것과 동일한 방식으로 MERGE 문에 대한 문제 해결 데이터를 수집합니다. 확장 이벤트 개요에 대한 자세한 내용은 빠른 시작: SQL Server의 확장 이벤트 및 SSMS XEvent Profiler 사용을 참조하세요.

예제

A. MERGE를 사용하여 단일 문의 테이블에 대해 INSERT 및 UPDATE 작업을 수행합니다.

일반적인 시나리오에서는 일치하는 행이 있으면 테이블에서 하나 이상의 열을 업데이트합니다. 또는 일치하는 행이 없으면 새 행으로 데이터를 테이블에 삽입합니다. 일반적으로 해당하는 UPDATE 및 INSERT 문이 포함된 저장 프로시저에 매개 변수를 전달하여 하나의 시나리오를 수행합니다. MERGE 문으로 단일 문에서 두 태스크를 수행할 수 있습니다. 다음 예제에서는 INSERT 문과 UPDATE 문을 모두 포함하는 AdventureWorks2022 데이터베이스의 저장 프로시저를 보여 줍니다. 해당 프로시저는 단일 MERGE 문을 사용하여 동일한 작업을 실행하기 위해 수정됩니다.

CREATE PROCEDURE dbo.InsertUnitMeasure @UnitMeasureCode NCHAR(3), @Name NVARCHAR(25)
AS
BEGIN
    SET NOCOUNT ON;

    -- Update the row if it exists.
    UPDATE Production.UnitMeasure
    SET Name = @Name
    WHERE UnitMeasureCode = @UnitMeasureCode

    -- Insert the row if the UPDATE statement failed.
    IF (@@ROWCOUNT = 0)
    BEGIN
        INSERT INTO Production.UnitMeasure (
            UnitMeasureCode,
            Name
        )
        VALUES (@UnitMeasureCode, @Name)
    END
END;
GO

-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Test Value';

SELECT UnitMeasureCode, Name
FROM Production.UnitMeasure
WHERE UnitMeasureCode = 'ABC';
GO

-- Rewrite the procedure to perform the same operations using the
-- MERGE statement.
-- Create a temporary table to hold the updated or inserted values
-- from the OUTPUT clause.
CREATE TABLE #MyTempTable (
    ExistingCode NCHAR(3),
    ExistingName NVARCHAR(50),
    ExistingDate DATETIME,
    ActionTaken NVARCHAR(10),
    NewCode NCHAR(3),
    NewName NVARCHAR(50),
    NewDate DATETIME
);
GO

ALTER PROCEDURE dbo.InsertUnitMeasure @UnitMeasureCode NCHAR(3),
    @Name NVARCHAR(25)
AS
BEGIN
    SET NOCOUNT ON;

    MERGE Production.UnitMeasure AS tgt
    USING (SELECT @UnitMeasureCode, @Name) AS src(UnitMeasureCode, Name)
        ON (tgt.UnitMeasureCode = src.UnitMeasureCode)
    WHEN MATCHED
        THEN
            UPDATE
            SET Name = src.Name
    WHEN NOT MATCHED
        THEN
            INSERT (UnitMeasureCode, Name)
            VALUES (src.UnitMeasureCode, src.Name)
    OUTPUT deleted.*,
        $action,
        inserted.*
    INTO #MyTempTable;
END;
GO

-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'New Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'XYZ', @Name = 'Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Another Test Value';

SELECT * FROM #MyTempTable;

-- Cleanup
DELETE FROM Production.UnitMeasure
WHERE UnitMeasureCode IN ('ABC', 'XYZ');

DROP TABLE #MyTempTable;
GO

CREATE PROCEDURE dbo.InsertUnitMeasure @UnitMeasureCode NCHAR(3),
    @Name NVARCHAR(25)
AS
BEGIN
    SET NOCOUNT ON;

    -- Update the row if it exists.
    UPDATE Production.UnitMeasure
    SET Name = @Name
    WHERE UnitMeasureCode = @UnitMeasureCode

    -- Insert the row if the UPDATE statement failed.
    IF (@@ROWCOUNT = 0)
    BEGIN
        INSERT INTO Production.UnitMeasure (
            UnitMeasureCode,
            Name
        )
        VALUES (@UnitMeasureCode, @Name)
    END
END;
GO

-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Test Value';

SELECT UnitMeasureCode, Name
FROM Production.UnitMeasure
WHERE UnitMeasureCode = 'ABC';
GO

-- Rewrite the procedure to perform the same operations using the
-- MERGE statement.
ALTER PROCEDURE dbo.InsertUnitMeasure @UnitMeasureCode NCHAR(3),
    @Name NVARCHAR(25)
AS
BEGIN
    SET NOCOUNT ON;

    MERGE Production.UnitMeasure AS tgt
    USING (
        SELECT @UnitMeasureCode,
            @Name
        ) AS src(UnitMeasureCode, Name)
        ON (tgt.UnitMeasureCode = src.UnitMeasureCode)
    WHEN MATCHED
        THEN
            UPDATE SET Name = src.Name
    WHEN NOT MATCHED
        THEN
            INSERT (UnitMeasureCode, Name)
            VALUES (src.UnitMeasureCode, src.Name);
END;
GO

-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'New Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'XYZ', @Name = 'Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Another Test Value';

-- Cleanup
DELETE FROM Production.UnitMeasure
WHERE UnitMeasureCode IN ('ABC', 'XYZ');
GO

B. MERGE를 사용하여 단일 문에서 테이블에 대한 UPDATE 및 DELETE 작업을 수행합니다.

다음 예제에서는 MERGE를 사용하여 테이블에서 처리되는 주문에 따라 AdventureWorks2022 샘플 데이터베이스의 SalesOrderDetail 테이블을 매일 업데이트 ProductInventory 합니다. Quantity 테이블의 ProductInventory 열은 SalesOrderDetail 테이블에서 매일 제품별로 접수되는 주문의 수를 빼는 방식으로 업데이트됩니다. 제품에 대한 주문 수로 인해 제품의 재고 수준이 0 이하로 떨어지면 해당 제품에 대한 행이 ProductInventory 테이블에서 삭제됩니다.

CREATE PROCEDURE Production.usp_UpdateInventory @OrderDate DATETIME
AS
MERGE Production.ProductInventory AS tgt
USING (
    SELECT ProductID,
        SUM(OrderQty)
    FROM Sales.SalesOrderDetail AS sod
    INNER JOIN Sales.SalesOrderHeader AS soh
        ON sod.SalesOrderID = soh.SalesOrderID
            AND soh.OrderDate = @OrderDate
    GROUP BY ProductID
    ) AS src(ProductID, OrderQty)
    ON (tgt.ProductID = src.ProductID)
WHEN MATCHED
    AND tgt.Quantity - src.OrderQty <= 0
    THEN
        DELETE
WHEN MATCHED
    THEN
        UPDATE
        SET tgt.Quantity = tgt.Quantity - src.OrderQty,
            tgt.ModifiedDate = GETDATE()
OUTPUT $action,
    Inserted.ProductID,
    Inserted.Quantity,
    Inserted.ModifiedDate,
    Deleted.ProductID,
    Deleted.Quantity,
    Deleted.ModifiedDate;
GO

EXECUTE Production.usp_UpdateInventory '20030501';

CREATE PROCEDURE Production.usp_UpdateInventory @OrderDate DATETIME
AS
MERGE Production.ProductInventory AS tgt
USING (
    SELECT ProductID,
        SUM(OrderQty)
    FROM Sales.SalesOrderDetail AS sod
    INNER JOIN Sales.SalesOrderHeader AS soh
        ON sod.SalesOrderID = soh.SalesOrderID
            AND soh.OrderDate = @OrderDate
    GROUP BY ProductID
    ) AS src(ProductID, OrderQty)
    ON (tgt.ProductID = src.ProductID)
WHEN MATCHED
    AND tgt.Quantity - src.OrderQty <= 0
    THEN
        DELETE
WHEN MATCHED
    THEN
        UPDATE
        SET tgt.Quantity = tgt.Quantity - src.OrderQty,
            tgt.ModifiedDate = GETDATE();
GO

EXECUTE Production.usp_UpdateInventory '20030501';

C. MERGE를 사용하여 파생 원본 테이블을 사용하여 대상 테이블에서 UPDATE 및 INSERT 작업을 수행합니다.

다음 예제에서는 MERGE를 사용하여 행을 업데이트하거나 삽입하여 AdventureWorks2022 데이터베이스의 테이블을 수정 SalesReason 합니다.

원본 테이블의 NewName 값이 대상 테이블 Name의 SalesReason 열에 있는 값과 일치하는 경우 대상 테이블에서 ReasonType 열이 업데이트됩니다. NewName 값이 일치하지 않으면 원본 행이 대상 테이블에 삽입됩니다. 원본 테이블은 Transact-SQL 테이블 값 생성자를 사용하여 원본 테이블의 여러 행을 지정하는 파생 테이블입니다. 파생 테이블에서 테이블 값 생성자를 사용하는 방법에 대한 자세한 내용은 테이블 값 생성자(Transact-SQL)를 참조하세요.

OUTPUT 절은 MERGE 문의 결과를 쿼리하는 데 유용할 수 있습니다. 자세한 내용은 OUTPUT 절을 참조하세요. 또한 이 예에서는 OUTPUT 절의 결과를 테이블 변수에 저장하는 방법을 보여 줍니다. 그런 다음, 삽입되거나 업데이트된 행의 개수를 반환하는 단순한 select 작업을 실행하여 MERGE 문의 결과를 요약합니다.

-- Create a temporary table variable to hold the output actions.
DECLARE @SummaryOfChanges TABLE (Change VARCHAR(20));

MERGE INTO Sales.SalesReason AS tgt
USING (
    VALUES ('Recommendation', 'Other'),
        ('Review', 'Marketing'),
        ('Internet', 'Promotion')
    ) AS src(NewName, NewReasonType)
    ON tgt.Name = src.NewName
WHEN MATCHED
    THEN
        UPDATE
        SET ReasonType = src.NewReasonType
WHEN NOT MATCHED BY TARGET
    THEN
        INSERT (Name, ReasonType)
        VALUES (NewName, NewReasonType)
OUTPUT $action
INTO @SummaryOfChanges;

-- Query the results of the table variable.
SELECT Change,
    COUNT(*) AS CountPerChange
FROM @SummaryOfChanges
GROUP BY Change;

원본 테이블의 NewName 값이 대상 테이블 Name의 SalesReason 열에 있는 값과 일치하는 경우 대상 테이블에서 ReasonType 열이 업데이트됩니다. NewName 값이 일치하지 않으면 원본 행이 대상 테이블에 삽입됩니다. 원본 테이블은 SELECT ... UNION ALL을 사용하여 원본 테이블의 여러 행을 지정하는 파생 테이블입니다.

MERGE INTO Sales.SalesReason AS tgt
USING (
    SELECT 'Recommendation', 'Other'
    UNION ALL    
    SELECT 'Review', 'Marketing'
    UNION ALL
    SELECT 'Internet', 'Promotion'
    ) AS src(NewName, NewReasonType)
    ON tgt.Name = src.NewName
WHEN MATCHED
    THEN
        UPDATE SET ReasonType = src.NewReasonType
WHEN NOT MATCHED BY TARGET
    THEN
        INSERT (Name, ReasonType)
        VALUES (NewName, NewReasonType);

D. MERGE 문의 결과를 다른 테이블에 삽입합니다.

다음 예에서는 MERGE 문의 OUTPUT 절에서 반환된 데이터를 캡처하고 이 데이터를 다른 테이블에 삽입합니다. MERGE 문은 테이블에서 처리되는 주문에 따라 AdventureWorks2022 데이터베이스의 SalesOrderDetail 테이블 열을 ProductInventory 업데이트 Quantity 합니다. 이 예에서는 업데이트된 행을 캡처하여 재고 변경 내용을 추적하는 데 사용되는 다른 테이블에 삽입합니다.

CREATE TABLE Production.UpdatedInventory (
    ProductID INT NOT NULL,
    LocationID INT,
    NewQty INT,
    PreviousQty INT,
    CONSTRAINT PK_Inventory PRIMARY KEY CLUSTERED (
        ProductID,
        LocationID
        )
    );
GO

INSERT INTO Production.UpdatedInventory
SELECT ProductID, LocationID, NewQty, PreviousQty
FROM (
    MERGE Production.ProductInventory AS pi
    USING (
        SELECT ProductID, SUM(OrderQty)
        FROM Sales.SalesOrderDetail AS sod
        INNER JOIN Sales.SalesOrderHeader AS soh
            ON sod.SalesOrderID = soh.SalesOrderID
                AND soh.OrderDate BETWEEN '20030701'
                    AND '20030731'
        GROUP BY ProductID
        ) AS src(ProductID, OrderQty)
        ON pi.ProductID = src.ProductID
    WHEN MATCHED
        AND pi.Quantity - src.OrderQty >= 0
        THEN
            UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
    WHEN MATCHED
        AND pi.Quantity - src.OrderQty <= 0
        THEN
            DELETE
    OUTPUT $action,
        Inserted.ProductID,
        Inserted.LocationID,
        Inserted.Quantity AS NewQty,
        Deleted.Quantity AS PreviousQty
    ) AS Changes(Action, ProductID, LocationID, NewQty, PreviousQty)
WHERE Action = 'UPDATE';
GO

E. MERGE를 사용하여 그래프 데이터베이스의 대상 에지 테이블에서 INSERT 또는 UPDATE를 수행합니다.

이 예제에서는 노드 테이블 Person 및 City와 에지 테이블 livesIn을 만듭니다. livesIn 에지가 Person 및 City 사이에 아직 없으면 이 에지에 대해 MERGE 문을 사용하여 새 행을 삽입합니다. 에지가 이미 있는 경우 livesIn 에지에 대해 StreetAddress 특성만 업데이트하면 됩니다.

-- CREATE node and edge tables
CREATE TABLE Person
(
    ID INTEGER PRIMARY KEY,
    PersonName VARCHAR(100)
)
AS NODE
GO

CREATE TABLE City
(
    ID INTEGER PRIMARY KEY,
    CityName VARCHAR(100),
    StateName VARCHAR(100)
)
AS NODE
GO

CREATE TABLE livesIn
(
    StreetAddress VARCHAR(100)
)
AS EDGE
GO

-- INSERT some test data into node and edge tables
INSERT INTO Person VALUES (1, 'Ron'), (2, 'David'), (3, 'Nancy')
GO

INSERT INTO City VALUES (1, 'Redmond', 'Washington'), (2, 'Seattle', 'Washington')
GO

INSERT livesIn SELECT P.$node_id, C.$node_id, c
FROM Person P, City C, (values (1,1, '123 Avenue'), (2,2,'Main Street')) v(a,b,c)
WHERE P.id = a AND C.id = b
GO

-- Use MERGE to update/insert edge data
CREATE OR ALTER PROCEDURE mergeEdge
    @PersonId integer,
    @CityId integer,
    @StreetAddress varchar(100)
AS
BEGIN
    MERGE livesIn
        USING ((SELECT @PersonId, @CityId, @StreetAddress) AS T (PersonId, CityId, StreetAddress)
                JOIN Person ON T.PersonId = Person.ID
                JOIN City ON T.CityId = City.ID)
        ON MATCH (Person-(livesIn)->City)
    WHEN MATCHED THEN
        UPDATE SET StreetAddress = @StreetAddress
    WHEN NOT MATCHED THEN
        INSERT ($from_id, $to_id, StreetAddress)
        VALUES (Person.$node_id, City.$node_id, @StreetAddress) ;
END
GO

-- Following will insert a new edge in the livesIn edge table
EXEC mergeEdge 3, 2, '4444th Avenue'
GO

-- Following will update the StreetAddress on the edge that connects Ron to Redmond
EXEC mergeEdge 1, 1, '321 Avenue'
GO

-- Verify that all the address were added/updated correctly
SELECT PersonName, CityName, StreetAddress
FROM Person , City , livesIn
WHERE MATCH(Person-(livesIn)->city)
GO

다음을 통해 공유

MERGE(Transact-SQL)

구문

인수

WITH <common_table_expression>

TOP ( expression ) [ PERCENT ]

database_name

schema_name

target_table

[ AS ] table_alias

USING <table_source>

[ AS ] table_alias

ON <merge_search_condition>

WHEN MATCHED THEN <merge_matched>

WHEN NOT MATCHED [ BY TARGET ] THEN <merge_not_matched>

WHEN NOT MATCHED BY SOURCE THEN <merge_matched>

AND <clause_search_condition>

<table_hint_limited>

INDEX ( index_val [ ,...n ] )

<output_clause>

OPTION ( <query_hint> [ ,...n ] )

<merge_matched>

UPDATE SET <set_clause>

Delete

<merge_not_matched>

( column_list )

VALUES ( values_list)

DEFAULT VALUES

<search_condition>

<graph search pattern>

설명

Azure Synapse Analytics 고려 사항

문제 해결

트리거 구현

사용 권한

인덱스 모범 사례

MERGE의 동시성 고려 사항

JOIN 모범 사례

매개 변수화 모범 사례

TOP 절 모범 사례

대량 로드 모범 사례

MERGE 성능 측정 및 진단

예제

A. MERGE를 사용하여 단일 문의 테이블에 대해 INSERT 및 UPDATE 작업을 수행합니다.

B. MERGE를 사용하여 단일 문에서 테이블에 대한 UPDATE 및 DELETE 작업을 수행합니다.

C. MERGE를 사용하여 파생 원본 테이블을 사용하여 대상 테이블에서 UPDATE 및 INSERT 작업을 수행합니다.

D. MERGE 문의 결과를 다른 테이블에 삽입합니다.

E. MERGE를 사용하여 그래프 데이터베이스의 대상 에지 테이블에서 INSERT 또는 UPDATE를 수행합니다.

관련 콘텐츠

피드백

추가 리소스