[MSSQL] merge

sqlserver 도 2008 버전부터 merge가 지원된다

MERGE TMAST03TT AS TARGET
USING (
  SELECT 'A' GUBUN, '2011' YEAR, 'F' SEASON, 'SA001' SCHOOL, 'B' BOKJONG, 'N' PUMOK, 'N' SAYANG, '' P_CODE, '1' P_CODE_VALUE1, '' ISSUE_DATE, '' ISSUE_ID
) AS SOURCE
ON (TARGET.GUBUN = SOURCE.GUBUN AND TARGET.YEAR = SOURCE.YEAR AND TARGET.SEASON = SOURCE.SEASON AND TARGET.SCHOOL = SOURCE.SCHOOL AND TARGET.BOKJONG = SOURCE.BOKJONG)
WHEN MATCHED THEN
  UPDATE SET TARGET.P_CODE_VALUE1 = SOURCE.P_CODE_VALUE1
WHEN NOT MATCHED BY TARGET THEN
  INSERT (GUBUN, YEAR, SEASON, SCHOOL, BOKJONG
    , PUMOK, SAYANG, P_CODE, P_CODE_VALUE1, ISSUE_DATE
    , ISSUE_ID
  )
  VALUES (SOURCE.GUBUN, SOURCE.YEAR, SOURCE.SEASON, SOURCE.SCHOOL, SOURCE.BOKJONG
    , SOURCE.PUMOK, SOURCE.SAYANG, SOURCE.P_CODE, SOURCE.P_CODE_VALUE1, SOURCE.ISSUE_DATE
    , SOURCE.ISSUE_ID)
OUTPUT $action, Inserted.*, Deleted.*
;