2017-12-20(MSSQL MERGE쿼리)

데이터 입력/수정/삭제를 한번에 처리할 수 있는 MERGE에 대해 알아보자

MERGE
단일 문에서 여러 DML(INSERT , UPDATE , DELETE)작업을 수행할 수 있다.

즉, 여러개의 개별 DML문을 단일 문으로 대체할 수 있다.
작업이 하나의 문 내에서 수행되면서, 데이터가 처리되는 횟수가 최소화 되고 쿼리 성능이 향상된다.

MERGE는 다음과 같이 사용한다.

merge 변경할테이블명 as A
    Using 기분테이블명 as B
    on A.컬럼명 = B.컬럼명
    WHEN MATCHED THEN
        일치할때 쿼리문
    WHEN NOT MANCHED THEN
        불일치할때 쿼리문

아래와 같이 기준테이블의 컬럼을 지정할 수 있다.
또한, MATCHED, NOTMATCHED와 함께 추가 조건 지정도 가능하다.

merge 변경될테이블명 as A
    Using (SELECT 컬럼명 FROM 기준테이블명) as B
    ON (A.컬럼명 = B.컬럼명 and A.컬럼명 = B.컬럼명)
    WHEN MATCHED AND 조건 THEN
        INSERT (A.컬럼명) VALUES (B.컬럼명)
    WHEN NOT MATCHED AND 조건 THEN
        UPDATE SET A.컬럼명 = B.컬럼명
    WHEN NOT MATCHED AND 조건 THEN
        DELETE;

예제를 살펴보자.

CREATE TABLE #TEMP_A(
    분류 varchar(20),
    제품 varchar(20),
    가격 int
)
GO
INSERT INTO #TEMP_A VALUES
('음식','우유',4000),
('음식','딸기',5000),
('학용품','연필',3000),
('의류','티셔츠',10000),
('의류','반바지',25000)
GO
CREATE TABLE #TEMP_B(
    사유 varchar(20),
    분류 varchar(20),
    제품 varchar(20),
    가격 int
)
GO
INSERT INTO #TEMP_B VALUES
('상함','음식','딸기',5000),
('새제품','학용품','지우개',1000),
('손상','의류','티셔츠',10000),
('손상','의류','반바지',25000)
 
SELECT * FROM #TEMP_A
SELECT * FROM #TEMP_B
 
--    검색 결과-- 
--        분류    |    제품    |    가격
--     1    음식    |    우유    |    4000
--    2    음식    |    딸기    |    5000
--    3    학용품    |    연필    |    3000
--    4    의류    |    티셔츠    |    10000
--    5    의류    |    반바지    |    25000
 
--    검색 결과--
--        사유    |    분류    |    제품    |    가격
--    1    상함    |    음식    |    딸기    |    5000
--    2    새제품    |    학용품    |    지우개    |    1000
--    3    손상    |    의류    |    티셔츠    |    10000
--    4    손상    |    의류    |    반바지    |    25000

다음과 같은 데이터를 가진 두개의 임시테이블을 생성해보자.

1.분류와 제품 컬럼을 기준으로 #TEMP_B와#TEMP_A에 동일한 값이 존재하지 않고,
#TEMP_B에서 사유컬럼의 값이 '새제품'이라면
해당 데이터를 #TEMP_A에 INSERT한다.

2.분류와제품 컬럼을 기준으로 #TEMP_B와#TEMP_A에 동일한 값이 존재하고,
#TEMP_B에서 사유컬럼의 값이 '손상'이라면
해당하는#TEMP_A의 데이터에서 제품명을 '수선한'+기존 제품명으로 UPDATE한다.

3.분류와 제품 컬럼을 기준으로 #TEMP_B와#TEMP_A에 동일한 값이 존재하고,
#TEMP_B에서 사유컬럼의 값이 '상함'이라면
#TEMP_A의 해당 데이터를 DELETE한다.

--1. 새제품 INSERT
INSERT INTO #TEMP_A
SELCT B.분류, B.제품, B.가격
FROM #TEMP_A A
    RIGHT JOIN #TEMP_B B ON A.분류 = B.분류 AND A.제품 = B.제품
WHERE A.분류 IS NULL
    AND B.사유 = '새제품'
 
--2. 손상제품 UPDATE
UPDATE #TEMP_A SET 제품 = '수선한' + A.제품
FROM #TEMP_A A
INNER JOIN
(
    SELECT B.분류, B.제품, B.가격
    FROM #TEMP_A A
        RIGHT JOIN #TEMP_B B ON A.분류 = B.분류 AND A.제품 = B.제품
    WHERE A.분류 IS NOT NULL
        AND B.사유 = '손상'
) B ON A.분류 = B.분류 AND A.제품 = B.제품 AND A.가격 = B.가격
 
--3. 상한 제품 DELETE
DELETE #TEMP_A FROM #TEMP_A A
INNER JOIN
(
    SELECT B.분류, B.제품, B.가격
    FROM #TEMP_A A
        RIGHT JOIN #TEMP_B B ON A.분류 = B.분류 AND A.제품 = B.제품
    WHERE A.분류 IS NOT NULL
        AND B.사유 = '상함'
) B ON A.분류 = B.분류 AND A.제품 = B.제품 AND A.가격 = B.

각각 INSERT , UPDATE , DELETE 세개의 쿼리문을 각각 작성해 보았다.

자 그럼 이제 이 3개의 쿼리를 MERGE를 사용하여 간단하게 다시 작성해보자.

MERGE #TEMP_A AS A
    --분류와 제품컬럼을 기준으로 비교
    USING (SELECT 사유,분류,제품,가격 FROM #TEMP_B)AS B
    ON (A.분류 = B.분류 AND A.제품 = B.제품)
 
    --1. 분류 컬럼과 제품 컬럼의 값이 일치한 행이 존재하지 않고,
    --     해당 행의 사유컬럼의 값이 '새제품'일경우
    WHEN NOT MATCHED AND B.사유 = '새제품' THEN
        INSERT (분류,가격,제품) VALUEs (B.분류, B.제품, B.가격)
 
    --2. 분류 컬럼과 제품 컬럼의 값이 일치한 행이 존재하고,
    --     해당 행의 사유컬럼의 값이 '손상'일경우
    WHEN MATCHED AND B.사유 = '손상' THEN
        UPDATE SET 제품 = ('수선한' + B.제품)
 
    --3. 분류 컬럼과 제품 컬럼의 값이 일치한 행이 존재하고,
    --     해당 행의 사유컬럼의 값이 '상함'일경우
    WHEN MATCHED AND B.사유 = '상함' THEN
        DELETE;

위와같이 작성이 가능해진다.