[MSSQL] MERGE를 사용하여 데이터 입력/수정/삭제를 한 번에 처리

출처 : https://ggmouse.tistory.com/130

[MSSQL] MERGE를 사용하여 데이터 입력/수정/삭제를 한 번에 처리

 

 

 

 

 

 

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

 

 

MERGE

 

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

 

즉, 여러 개의 개별 DML문을 단일 문으로 대체할 수 있다.

 

작업이 하나의 문 내에서 수행되면서, 데이터가 처리되는 횟수가 최소화되고 쿼리 성능이 향상된다.

 

 

 

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

 

MERGE 변경될테이블명 AS A
    USING 기준테이블명 AS B
    ON A.컬럼명 = B.컬럼명
    WHEN MATCHED THEN
        일치할때쿼리문
    WHEN NOT MATCHED THEN
        불일치할때쿼리문

 

 

아래와 같이 기준 테이블의 칼럼을 지정할 수 있다.

 

또한, MATCHED, NOT MATCHED와 함께 추가 조건 지정도 가능하다.

 

 

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. 분류와 제품 컬럼을 기준으로 #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
SELECT 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;

 

#TEMP_A 결과

 

 

 

[MSSQL] NOT MATCHED BY SOURCE ('DELETE' 유형의 동작은 MERGE 문의 'WHEN NOT MATCHED' 절에 사용할 수 없습니다.)