제2의 비엔지니어 인생관을 꿈꾸며

Posted
Filed under MSSQL
MSSQL 프로그래밍에세  꽤나 많은 부분을 CRUD 구현하는데, 많은 문장을 작성해 나간다.
WITH 가 Select 에 최적화되어 있다면 MERGE 조인된 테이블의 Insert / Update /Delete 를 구현하는데 최적화 되어있다고 보면 좋겠다.

# MERGE 의 기본 문법 및 의미
MERGE  [Target_Table]  AS TT   -- TT 는 [Target_Table] 의 alias
USING  [Source_Table]  AS TS   -- TS 는 [Source_Table]의 alias
ON ( TT.Column1 = TS.Column1 )  

WHEN MATCHED 
          THEN  [Target_Table]에 대한 처리

WHEN NOT MATCHED BY TARGET   AND  TS.RegDate > '2018-01-01'
           THEN 
[Source_Table] 에 있고 , [Target_Table] 에 없으면, [Target_Table] 에  Insert / Update

WHEN 
NOT MATCHED  BY SOURCE  AND  TT.RegDate < '2018-01-01'
          
THEN  [Source_Table] 에 없고,  [Target_Table] 에 있으면,   [Target_Table]  꺼 처리  Delete / Update

OUTPUT   $action , inserted.* , deleted.*  ;   //  로그보기  MERGE문의 종료는  반디스 세미콜론을  해주어야 한다.
(inserted 는 insert / update 둘다의 의미를 가지고 있으므로 , 따로 updated는 없다.)


2개의 연관된 테이블의 데이터가 "NOT MATCHED" 이긴한데 명시적으로 아래와같이 이용때
  BY TARGET   :  Source 에는 있고, Target 에 없으면
  BY SOURCE  :  Source 에는 없고, Target 에 있을때

 주의  :  WHEN MATCHED 를 제외하고  WHEN NOT MATCHED BY TARGET  처리시  WHEN NOT MATCHED  BY SOURCE 조건도 만족한다면 2개처리한다.   (WHEN MATCHED 를 제외)
1개의 WHEN 조건을 처리하고 종료하는 구조가 아님 , TARGET 과 SOURCE를 2개다 처리하게된다.

예를 들어 BY TARGET 처리 후 BY SOURCE 조건도 만족한다면 SOURCE 조건도 처리함 (코드의 순서와 관계없음)




# 테스트 테이블 및 데이터 만들기
-- 테이블 생성
create table #tbl1 (
 idx tinyint ,  name varchar(20) )
create table #tbl2 ( idx tinyint ,  name varchar(20) )


-- 더미 INSERT
insert #tbl1 values( 1 , 'aaa')
insert #tbl1 values( 2 , 'bbb')

insert #tbl2 values( 3 , 'ccc')
insert #tbl2 values( 4 , 'ddd')
insert #tbl2 values( 5 , 'eee')

-- 데이터 확인
select * From #tbl1
go
select * From #tbl2


# 간략하게 MERGE 이용
MERGE #tbl2 As TT -- Target
USING #tbl1 As TS -- Source
ON (TT.idx = TS.idx)
-- Source 에는 있고 Target 에 있으면, Source 참조로 UPDATE
WHEN MATCHED 
THEN UPDATE SET TT.NAME = TS.NAME
WHEN NOT MATCHED -- BY TARGET 생략시  NOT MATCHED BY TARGET  과 동일
THEN INSERT (idx ,name ) values(TS.idx , TS.name)




# BY TARGET  ,BY SOURCE 를 이용하여, 명시적으로처리
MERGE #tbl2 TT -- Target
USING #tbl1 TS -- Source
ON (TT.idx = TS.idx)
-- Source 에는 있고 Target 에 있으면, Source 참조로 UPDATE
WHEN MATCHED 
THEN UPDATE SET TT.NAME = TS.NAME

-- Source 에는 있고 Target 에 없으면, Source 참조로 Target 에INSERT
WHEN NOT MATCHED BY TARGET  -- AND TS.idx = 5  조건 추가 할 수 있음
THEN INSERT (idx ,name ) values(TS.idx , TS.name)

-- Source 에는 없고, Target 에 있으면 Target 에 Delete or Update
WHEN NOT MATCHED BY SOURCE -- AND TT.idx  >= 2 조건 추가 할 수있음
THEN Delete   -- or THEN UPDATESet idx = 1000 
OUTPUT   $action , inserted.* ,   deleted.*;   -- 로그사용

위 쿼리 실행 


[#tbl1 과 #tbl2 원본 Select]



[위의 Merge 실행후 Log]



[최종결과]
빨강네모 -  NOT MATCHED BY TARGET   - Insert
파랑네모 - NOT MATCHED BY SOURCE  - Delete




[최종결과]

빨강네모 -  NOT MATCHED BY TARGET   를 통해 Insert 만하고 종료하지 않고 ,
파랑네모 - NOT MATCHED BY SOURCE  조건도 만족하는것을 감지하는순간 Delete 도 동시에 처리한다.

정리하자면,
일단 Merge ~ Using ~ On 조건을 통해 Not Matched 조건이 발생하면 1개의 Not Matched(BY TARGET) 의 조건만
처리하지 않고, 
처리 후 다른 Not Matched(BY SOURCE) 조건에 만족하면 연속해서 발생한다.
( 위에 2조건을 만족하게되면 MATCHED 도 만족하게 되지만,  MATCHED 는 일어나지 않음 )
2018/04/02 18:27 2018/04/02 18:27