mssql 에서 merge문사용(insert + update)

일반적으로 스케쥴이나 타DB 연동, 대량데이터 insert 작업을 수행하는 경우 기존 자료와 중복체크 후 작업해야 하는 경우가 꽤 있습니다.

 

보통은 해당 자료에 대한 select 작업으로 중복을 체크한 후 중복이 없을 경우 insert 하고 중복이 있는 경우 update 작업을 합니다.

 

이 작업이 비효율적이라고 판단될 경우 해당 자료와 중복되는 자료를 delete 처리하고 insert 하는 경우도 꽤 있습니다.

 

이런 작업에 대해 가장 적합한 쿼리는 제 생각에는 merge 문의 사용입니다.

merge 문은 조건(키)에 해당하는 자료가 존재할경우 update 처리를 존재하지 않을 경우 insert 처리를 해주는 쿼리로 한문장으로 모든 작업을 처리 합니다.

물론 대용량(10만건이상? 100만건이상?) 작업을 진행할 경우 merge문은 CPU점유율이 매우 올라가고 작업속도도 매우 안좋은 단점이 있지만 중소규모 작업(10만건 이하)에서는 매우 유용한 쿼리 입니다.

 

MSSQL에서 권한 관련 내용은 잘 생각하지 않는 경우가 많지만 select, insert, update, delete 권한 모두 필요합니다.

 

쿼리형식

declare @USER_ID varchar(20)  ,@USER_CD varchar(20) 

set @USER_ID = 'aaaa' 
set @USER_CD = 'bbbb' 

MERGE MY_USER as TB1 
   USING (VALUES (@USER_ID, @USER_CD)) as tb2(USER_ID,USER_CD) 
     ON tb1.USER_ID = tb2.USER_ID
           and tb1.USER_CD = tb2.USER_CD 
   WHEN MATCHED THEN 
      UPDATE SET SERVICE_NO = 'dddd' 
         , USE_YN = 'N' 
         , INS_DATE = getdate()  
      WHEN NOT MATCHED THEN  
         INSERT (USER_ID, USER_CD, SERVICE_NO, USE_YN , INS_DATE) 
         VALUES(@USER_ID, @USER_CD , 'ccccc', 'Y' , getdate());

 

대략적으로 설명하면

MERGE MY_USER as TB1 
   USING (VALUES (@USER_ID, @USER_CD)) as tb2(USER_ID,USER_CD) 
     ON tb1.USER_ID = tb2.USER_ID
           and tb1.USER_CD = tb2.USER_CD 

 

MY_USER 이라는 테이블이 TB1

외부에서 입력받은 @USER_ID, @USER_CD 두개의 컬럼으로 구성된 테이블이 TB2 가 됩니다.

이 두 테이블에서 tb1.user_id와 tb2.user_id 가 동일한지 비교합니다. (조건1)

이 두 테이블에서 tb1.user_cd와 tb2.user_cd 가 동일한지 비교합니다. (조건2)

물론 조건은 1개가 되어도 되고 더 많은 조건을 주어되 됩니다.

 

 

   WHEN MATCHED THEN 
      UPDATE SET SERVICE_NO = 'dddd' 
         , USE_YN = 'N' 
         , INS_DATE = getdate()  

2개의 조건을 비교한 결과 일치하면 update 문을 실행합니다.

merge 문에서 테이블명을 지정했고 조건을 주었으므로 update 문에는 각 칼럼의 데이터만 지정합니다.

 

 

      WHEN NOT MATCHED THEN  
         INSERT (USER_ID, USER_CD, SERVICE_NO, USE_YN , INS_DATE) 
         VALUES(@USER_ID, @USER_CD , 'ccccc', 'Y' , getdate());

 

만약 조건이 일치하지 않는다면 insert문을 실행해줍니다.

insert 문도 update 문과 마찬가지로 테이블명은 merge문에서 지정을 했기 때문에 isnert 컬럼과 데이터만 지정해줍니다.

 

 

 

 

 

 

java 에서 활용할때는 다음과 같이 사용합니다.

sqlText  = " MERGE MY_USER as TB1  \n"; 
sqlText  += " USING (VALUES (?,?)) as tb2(USER_ID,USER_CD)   \n"; 
sqlText  += " ON tb1.USER_ID = tb2.USER_ID  \n";

sqlText  += "   and tb1.USER_CD = tb2.USER_CD  \n"; 
sqlText  += "  WHEN MATCHED THEN \n"; 
sqlText  += "     UPDATE SET SERVICE_NO = ? \n"; 
sqlText  += "     , USE_YN= ? \n"; 
sqlText  += "     , INS_DATE = getdate() \n"; 
sqlText  += " WHEN NOT MATCHED THEN \n"; 
sqlText  += "  INSERT (USER_ID, USER_CD, SERVICE_NO, USE_YN , INS_DATE) \n"; 
sqlText  += "  VALUES(?, ?, ?, ? , getdate()) ;\n";


PreparedStatement ps = Dbcon.prepareStatement(sqlText); 
ps.setString(1, temp1); 
ps.setString(2, temp2);


ps.setString(3, temp3); 
ps.setString(4, temp4);



ps.setString(5, temp5); 
ps.setString(6, temp6); 
ps.setString(7, temp7); 
ps.setString(8, temp8);


ps.executeUpdate();