MSSQL - MERGE - 데이터 동기화, 조건에 따라 일괄 데이터 삽입/수정하기

 

 

MERGE

개념

• 원본 테이블과의 조인 결과를 기반으로 대상 테이블에서 삽입, 업데이트 또는 삭제 작업을 실행
• SQL의 'INSERT', 'UPDATE', 'DELETE' 작업을 조건에 따라 하나의 문으로 통합하여 처리하는 구문
• 주로 두 개의 테이블을 비교하고 데이터를 동기화하는데 사용

---

MERGE를 사용하는 상황에 따른 이점

• 두 개의 테이블 간 데이터를 동기화 해야 할 때
• 조건에 따라 특정 레코드를 업데이트하거나 삽입해야 할 때
• 대량의 데이터를 효율적으로 처리해야 할 때

---

구문

MERGE INTO target_table AS target
	USING source_table AS source
		ON <condition>
	WHEN MATCHED THEN
    	    <update_statement>
    	    -- or <delete_statement>
	WHEN NOT MATCHED THEN
    	    <insert_statement>
	WHEN NOT MATCHED BY SOURCE THEN
    	    <update_statement>
    	    -- or <delete_statement>

• target_table : 테이터를 삽입하거나 업데이트할 대상 테이블
• source_table : 데이터를 가져온 소스 테이블
• <condition> : 소스 테이블과 대상 테이블을 비교하여 데이터를 동기화할 조건 지정
•  WHEN MATCHED THEN : 소스 테이블과 대상 테이블의 행의 값이 일치할 경우, 값을 업데이트하거나 삭제
• WHEN NOT MATCHED THEN : 소스 테이블과 대상 테이블의 행의 값이 일치하지 않을 경우, 값 삽입
• WHEN NOT MATCHED BY SOURCE THEN : 소스 테이블과 대상 테이블의 행이 일치하지 않을 경우, 값을 업데이트하거나 삭제
• <update_statement> : 조건에 맞는 레코드가 이미 대상 테이블에 존재할 경우 실행할 업데이트 작업 지정
• <insert_statement> : 조건에 맞는 레코드가 대상 테이블에 존재하지 않을 경우 실행할 삽입 작업 지정
• <delete_statement> : 대상 테이블에 존재하고 소스 테이블에 없는 레코드를 삭제할 경우 실행할 삭제 작업 지정

 

조금 더 간단히 하자면,

MERGE
	INTO -- 변경할테이블명
    	USING -- 비교할테이블명
    ON
    	-- ON 이후에는 조건 설정
        WHEN MATCHED -- 조건 : 일치하는 행이 존재할 경우
        	THEN -- UPDATE 혹은 DELETE 작업 수행
            -- 예시) UPDATE ~~~ SET ~~~ WHERE ~~~
        WHEN NOT MATCHED -- 조건 : 일치하는 행이 존재하지 않을 경우
        	THEN -- INSERT 작업 수행
            -- 예시) INSERT INTO ~~~ (~~~) VALUES (~~~)
        WHEN MATCHED BY SOURCE -- 조건 : origin에는 있지만 new에는 존재하지 않을 경우
        	THEN -- UPDATE 혹은 DELETE 작업 수행

---

예시 (샘플 코드)

대상 테이블 : originTable

insert into originTable
	select 'eunbyeol', 'incheon'

 

비교할 테이블 : newTable

insert into newTable
	select 'eunbyeol', 'seoul'
	union all
	select 'tester1', 'inchoen'

 

두 개의 테이블의 데이터를 동기화 하려면 다음과 같은 조건을 수행해야 합니다.

1. UPDATE의 경우(혹은 DELETE)
   두 개의 테이블 모두 id가 1이고 이름이 동일하지만
   주소가 원본 테이블에서는 인천, 비교할 테이블에서는 서울로 각각 다르다.
   >>> 이럴 경우에는 원본 테이블을 인천에서 서울로 수정하여야 한다.
2. INSERT의 경우
   원본 테이블에서 id가 2인 행은 없고
   비교할 테이블에서 id가 2인 행이 있다.
   >>> 이럴 경우에는 원본 테이블에 값을 삽입하여야 한다.

 

조건에 맞게 MERGE 문을 작성해봅시다.

MERGE INTO originTable As origin -- 원본 테이블 (값을 동기화할 테이블)
	USING newTable as new -- 대상 테이블 (값을 비교할 테이블)
ON origin.id = new.id
	WHEN MATCHED THEN -- id 값이 일치하나 다른 컬럼의 값이 다를 경우 업데이트 수행
		UPDATE SET origin.name = new.name, origin.address = new.address
	WHEN NOT MATCHED THEN -- id 값이 일치하지 않는 경우 값 삽입 수행
		INSERT (name, address)
		VALUES (new.name, new.address); -- MERGE문은 세미콜론으로 구문의 종료를 알려야 함
	-- WHEN NOT MATCHED BY SOURCE THEN -- id 값이 일치하지 않는데 origin에만 값이 있는 경우 삭제 수행
		-- DELETE;

 

작성된 쿼리가 올바르게 실행되었는지 확인해봅시다.

select * from originTable

 

• 확인
  • id 1 : 주소가 달랐으므로 인천에서 서울로 UPDATE 수행
  • id 2 : id 2의 행이 없었으므로 해당 행의 데이터 INSERT 수행

 

정상적으로 잘 수행된 것을 확인할 수 있습니다.

---

조건이 있을 경우

특정 컬럼의 값만을 MERGE 하고 싶은 경우

• id의 값이 1인 경우에만 MERGE 실행
• USING 조건 추가
  • 서브쿼리를 이용하여 대상 테이블의 값을 id로 한정하여 출력
  • 예시) select * from newTable where id = 1
• ON 조건 추가
  • 원본 테이블의 값을 id로 한정하여 출력
  • 예시) on origin.id = 1

MERGE INTO originTable As origin -- 원본 테이블 (값을 동기화할 테이블)
	USING (SELECT * FROM newTable WHERE id = 1) as new -- 대상 테이블 (값을 비교할 테이블)
ON origin.id = 1
	WHEN MATCHED THEN -- id 값이 일치하나 다른 컬럼의 값이 다를 경우 업데이트 수행
		UPDATE SET origin.name = new.name, origin.address = new.address
	WHEN NOT MATCHED THEN -- id 값이 일치하지 않는 경우 값 삽입 수행
		INSERT (name, address)
		VALUES (new.name, new.address); -- MERGE문은 세미콜론으로 구문의 종료를 알려야 함

---

특정 컬럼의 값이 동일하지만 여러행이 존재할 경우

• id의 값이 1이고, id의 값이 1인 행이 여러행 존재할 경우 추가 조건을 주어 MERGE 실행
• ON 에서 다른 추가 조건을 정의, 예를 들면 id가 같지만 이름이 다른 경우라면 id와 name을 동일시하면 됩니다.
  • 예시) origin.id = new.id and origin.name = new.name

MERGE INTO originTable As origin -- 원본 테이블 (값을 동기화할 테이블)
	USING (SELECT * FROM newTable WHERE id = 1) as new -- 대상 테이블 (값을 비교할 테이블)
ON origin.id = new.id and origin.name = new.name
	WHEN MATCHED THEN -- id 값이 일치하나 다른 컬럼의 값이 다를 경우 업데이트 수행
		UPDATE SET origin.name = new.name, origin.address = new.address
	WHEN NOT MATCHED THEN -- id 값이 일치하지 않는 경우 값 삽입 수행
		INSERT (name, address)
		VALUES (new.name, new.address); -- MERGE문은 세미콜론으로 구문의 종료를 알려야 함

 

위의 쿼리는 하나의 단점이 있습니다.

원본 테이블의 id가 1인 다른 행이 존재하지만 비교 테이블에는 원본 테이블의 행보다 적을 경우,

나머지 행은 적용되지 않습니다. 

비교 테이블의 특정 값의 행(id = 1일 경우)의 모든 데이터의 수를 맞춰주는 쿼리는 아래와 같습니다.

예) 원본 테이블 id가 1인 행이 총 10개, 비교 테이블 id가 1인 행이 총 5개이면 일치하는 id와 name만 merge하므로 나머지 id와 name이 일치하지 않는 행은 merge가 일어나지 않습니다. 그러므로 일치하지 않는 행을 원본 테이블에서 삭제해야할 경우(즉, 데이터 동기화) 'WHEN NOT MATCHED BY SOURCE' 키워드를 추가하여 삭제할 수 있습니다. 

• id 값이 1인 행들 중, id와 name이 일치하지 않는 행 삭제
  • when not matched by source 키워드를 추가하고 and 뒤에 조건을 추가합니다. 
  • 예시) when not matched by source and origin.id = 1

MERGE INTO originTable As origin -- 원본 테이블 (값을 동기화할 테이블)
	USING (SELECT * FROM newTable WHERE id = 1) as new -- 대상 테이블 (값을 비교할 테이블)
ON origin.id = new.id and origin.name = new.name
	WHEN MATCHED THEN -- id 값이 일치하나 다른 컬럼의 값이 다를 경우 업데이트 수행
		UPDATE SET origin.name = new.name, origin.address = new.address
	WHEN NOT MATCHED THEN -- id 값이 일치하지 않는 경우 값 삽입 수행
		INSERT (name, address)
		VALUES (new.name, new.address)
    WHEN NOT MATCHED BY SOURCE AND origin.id = 1 THEN
        DELETE; -- MERGE문은 세미콜론으로 구문의 종료를 알려야 함

---

참고

• Microsoft - Learn - SQL - MERGE(Transact-SQL)