Microsoft SQL Server
병합

소개

통사론

• MSDN에 따라 - https://msdn.microsoft.com/en-us/library/bb510625.aspx [WITH <common_table_expression> [, ... n]] MERGE [TOP (expression) [PERCENT]] [INTO] < 표제어] [표제어] [표제어] [표제어] [WITH (<merge_history>)] [[AS] 표 _ 별칭) USING <table_source> ON <merge_search_condition> [언제 [AND <clause_search_condition>] THEN <merge_matched> [... n] [NOT BY MATCHED [BY THERE <merge_matches>] [OPEN (<query_hint> [,.])에 의해 매치되지 않을 때 [TARGET] [AND <clause_search_condition>] THEN <merge_not_matched> ..n])]; <target_table> :: = {[database_name. schema_name. | schema_name. ], {{...}]}}} <table_source> :: = {table_or_view_name [target_number]] { [AS] table_alias] [<tablesample_clause>] [WITH (table_hint [[,] ... n])] | rowset_function [[AS] table_alias] [(bulk_column_alias [, ... n])] | user_defined_function [[AS] 테이블 _ 별칭] | 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 = 표현식} | method_name (인수 [, ... n])}} | column_name {.WRITE (표현식, @Offset, @Length)} | @variable = 표현식 | @variable = column = expression | column_name {+ = | - = | * = | / = | % = | & = | ^ = | | =} 표현식 | @ 변수 {+ = | - = | * = | / = | % = | & = | ^ = | | =} 표현식 | @variable = column {+ = | - = | * = | / = | % = | & = | ^ = | | =} 표현식} [, ... n] <merge_not_matched> :: = {INSERT [(column_list)] {VALUES (values_list) | 기본 값}} <절 검색 조건> :: = <검색 조건> :: = {[NOT] | (<search_condition>)} [{AND | 또는} [NOT] {| (<search_condition>)}] [, ... n] :: = {식 {= | <> | ! = |

  | > = | ! > | <| <= | ! <} 표현식 | string_expression [NOT] LIKE string_expression [ESCAPE 'escape_character'] | 표현식 [NOT] BETWEEN 표현식과 표현식 | 표현식은 [NOT] NULL | 포함 ({column | *}, '<contains_search_condition>') | FREETEXT ({column | *}, 'freetext_string') | expression [NOT] IN (하위 쿼리 | 식 [, ... n]) | 표현식 {= | <> | ! = | | > = | ! > | <| <= | ! <} {전체 | 일부 | ANY} (하위 쿼리) | EXISTS (하위 쿼리)} <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 | 삽입 | from_table_name}. {* | column_name} | $ action

비고

삽입 / 업데이트 / 삭제할 MERGE

MERGE INTO targetTable

USING sourceTable 
ON (targetTable.PKID = sourceTable.PKID)

WHEN MATCHED AND (targetTable.PKID > 100) THEN
    DELETE

WHEN MATCHED AND (targetTable.PKID <= 100) THEN 
    UPDATE SET 
        targetTable.ColumnA = sourceTable.ColumnA, 
        targetTable.ColumnB = sourceTable.ColumnB

WHEN NOT MATCHED THEN
    INSERT (ColumnA, ColumnB) VALUES (sourceTable.ColumnA, sourceTable.ColumnB);

WHEN NOT MATCHED BY SOURCE THEN
    DELETE
; --< Required

기술:

• MERGE INTO targetTable - 수정 될 테이블
• USING sourceTable - 데이터 소스 (테이블 또는 뷰 또는 테이블 값 함수 일 수 있음)
• ON ... - targetTable 과 sourceTable 사이의 조건을 조인합니다.
• WHEN MATCHED - 일치하는 항목이 발견되었을 때 수행 할 작업
• • AND (targetTable.PKID > 100) - 조치를 취하기 위해 충족되어야하는 추가 조건
• THEN DELETE - targetTable 에서 일치하는 레코드 삭제
• THEN UPDATE - SET .... 지정된 일치하는 레코드의 열을 업데이트합니다 SET ....
• WHEN NOT MATCHED - targetTable 에서 일치하는 항목을 찾을 수없는 경우 수행 할 작업입니다.
• WHEN NOT MATCHED BY SOURCE 와 일치하지 않는 경우 - sourceTable 에서 일치하는 항목을 찾을 수없는 경우 수행 할 작업

코멘트:

특정 조치가 필요하지 않은 경우 조건을 생략하십시오. 예를 들어, 일치하지 않을 때를 제거하십시오. WHEN NOT MATCHED THEN INSERT 는 레코드가 삽입되지 않도록합니다.

병합 명령문은 종료 세미콜론이 필요합니다.

제한 사항 :

• WHEN MATCHED 가 INSERT 동작을 허용하지 않는 경우
• UPDATE 는 행을 한 x 만 갱신 할 수 있습니다. 이는 조인 조건이 고유 한 일치를 생성해야 함을 의미합니다.

CTE 소스를 사용하여 병합

WITH SourceTableCTE AS
(
    SELECT * FROM SourceTable
)
MERGE  
 TargetTable AS target
USING SourceTableCTE AS source  
ON (target.PKID = source.PKID)
WHEN MATCHED THEN     
    UPDATE SET target.ColumnA = source.ColumnA
WHEN NOT MATCHED THEN
    INSERT (ColumnA) VALUES (Source.ColumnA);

파생 소스 테이블을 사용하는 MERGE

MERGE INTO TargetTable  AS Target  
USING (VALUES (1,'Value1'), (2, 'Value2'), (3,'Value3'))  
       AS Source (PKID, ColumnA)  
ON Target.PKID = Source.PKID 
WHEN MATCHED THEN 
    UPDATE SET target.ColumnA= source.ColumnA
WHEN NOT MATCHED THEN
    INSERT (PKID, ColumnA) VALUES (Source.PKID, Source.ColumnA);

병합 예제 - 소스 및 목표 테이블 동기화

MERGE 선언문을 나타내려면 다음 두 테이블을 고려하십시오 -

1. dbo.Product :이 테이블에는 회사에서 현재 판매중인 제품에 대한 정보가 들어 있습니다.

2. dbo.ProductNew :이 테이블에는 회사에서 향후 판매 할 제품에 대한 정보가 들어 있습니다.

다음 T-SQL은이 두 테이블을 만들고 채 웁니다.

IF OBJECT_id(N'dbo.Product',N'U') IS NOT NULL 
DROP TABLE dbo.Product
GO

CREATE TABLE dbo.Product (
ProductID INT PRIMARY KEY,
ProductName NVARCHAR(64),
PRICE MONEY
)

IF OBJECT_id(N'dbo.ProductNew',N'U') IS NOT NULL 
DROP TABLE dbo.ProductNew
GO

CREATE TABLE dbo.ProductNew (
ProductID INT PRIMARY KEY,
ProductName NVARCHAR(64),
PRICE MONEY
)

INSERT INTO dbo.Product VALUES(1,'IPod',300)
,(2,'IPhone',400)
,(3,'ChromeCast',100)
,(4,'raspberry pi',50)

INSERT INTO dbo.ProductNew VALUES(1,'Asus Notebook',300)
,(2,'Hp Notebook',400)
,(3,'Dell Notebook',100)
,(4,'raspberry pi',50)

이제 dbo.ProductNew 테이블과 함께 dbo.Product Target Table을 동기화하려고한다고 가정합니다. 이 작업의 기준은 다음과 같습니다.

1. dbo.ProductNew 원본 테이블과 dbo.Product 대상 테이블에 모두있는 제품은 dbo.Product 대상 테이블에서 새로운 새 제품으로 업데이트됩니다.

2. dob.ProductNew 소스 테이블의 dob.Product 목표 테이블에없는 제품은 dbo.Product 목표 테이블에 삽입됩니다.

3. dbo.ProductNew 원본 테이블에 존재하지 않는 dbo.Product 대상 테이블의 모든 제품은 dbo.Product 대상 테이블에서 삭제해야합니다. 이 작업을 수행하는 MERGE 문은 다음과 같습니다.

MERGE dbo.Product AS SourceTbl 
USING dbo.ProductNew AS TargetTbl ON (SourceTbl.ProductID = TargetTbl.ProductID)
WHEN MATCHED 
            AND SourceTbl.ProductName <> TargetTbl.ProductName
            OR SourceTbl.Price <> TargetTbl.Price
    THEN UPDATE SET SourceTbl.ProductName = TargetTbl.ProductName,
                SourceTbl.Price = TargetTbl.Price
WHEN NOT MATCHED 
    THEN INSERT (ProductID, ProductName, Price)
         VALUES (TargetTbl.ProductID, TargetTbl.ProductName, TargetTbl.Price)
WHEN NOT MATCHED BY SOURCE 
    THEN DELETE
OUTPUT $action, INSERTED.*, DELETED.*;

참고 : 세미콜론은 MERGE 문 끝 부분에 있어야합니다.

EXCEPT를 사용하여 병합

변경되지 않은 레코드를 업데이트하지 않으려면 EXCEPT를 사용하십시오.

MERGE TargetTable targ
USING SourceTable AS src
    ON src.id = targ.id
WHEN MATCHED
    AND EXISTS (
        SELECT src.field
        EXCEPT
        SELECT targ.field
        )
    THEN
        UPDATE
        SET field = src.field
WHEN NOT MATCHED BY TARGET
    THEN
        INSERT (
            id
            ,field
            )
        VALUES (
            src.id
            ,src.field
            )
WHEN NOT MATCHED BY SOURCE
    THEN
        DELETE;