[MSSQL] MERGE

MERGE

• UPSERT 명령어를 수행하는데 사용하는 강력한 (Transact-SQL) 명령어
• TARGET 테이블의 값을 조건에 따라 변경한다.

MERGE TOP (value) <target_table>
USING <table_source>  
ON <merge_search_condition>  
    [ WHEN MATCHED [ AND <clause_search_condition> ]  
        THEN <merge_matched> ]
    [ 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> ]
    [ <output_clause> ]  
    [ OPTION ( <query_hint> ) ]      

Syntax 로는 어떻게 사용하는지 파악하기 힘들기 때문에 예시를 통해 사용방법을 알아본다.

기본 예시

예제 테이블

DROP TABLE #Target;
DROP TABLE #Source;

-- Creating the Target temporary table
CREATE TABLE #Target (
    idx INT,
    product VARCHAR(20),
    qty INT
);

-- Inserting some sample data into Target temporary table
INSERT INTO #Target (idx, product, qty)
VALUES
    (1, 'ABC123', 50),
    (2, 'XYZ789', 75),
	(3, 'GHI789', 10);

CREATE TABLE #Source (
    idx INT,
    product VARCHAR(20),
    qty INT
);

-- Inserting some sample data into Source temporary table
INSERT INTO #Source (idx, product, qty)
VALUES
    (1, 'ABC123', 100);

SELECT * FROM #Target;
SELECT * FROM #Source;

• TARGET

IDX	PRODUCT	QTY
1	'ABC123'	50
2	'XYZ789'	75
3	'GHI789'	10

• SOURCE

IDX	PRODUCT	QTY
1	'ABC123'	100

UPSERT

MERGE #Target AS T
USING #Source AS S
	ON T.product = S.product
WHEN MATCHED THEN 
	UPDATE SET qty = S.qty
WHEN NOT MATCHED THEN
	INSERT (idx, product, qty) VALUES (S.idx, S.product, S.qty);

Dummy table 을 이용한 방법

MERGE #Target AS T
USING (SELECT 1 AS DUAL)AS D
	ON T.idx = 1
WHEN MATCHED THEN 
	UPDATE SET qty = 100
WHEN NOT MATCHED THEN
	INSERT (idx, product, qty) VALUES (1, 'ABC123', 50);

VALUES 와 변수를 이용한 방법

DECLARE @IDX INT = 1;
DECLARE @PRODUCT NVARCHAR(10) = 'ABC123';
DECLARE @QTY INT = 100;


MERGE #Target AS T
USING (VALUES(@IDX, @PRODUCT, @QTY)) as S(idx, product, qty)
	ON T.idx = 1
WHEN MATCHED THEN 
	UPDATE SET qty = 100
WHEN NOT MATCHED THEN
	INSERT (idx, product, qty) VALUES (1, 'ABC123', 50);

• UPDATE 경우

BEFORE

AFTER

• INSERT 경우

BEFORE

AFTER

 

Reference

SQL Server MERGE Statement overview and examples

---

There might be incorrect information or outdated content.