MERGE INTO ESAOGDP AS TRGT
USING (SELECT '100' AS SYS_ID
, 'C0100' AS COMP_CD
, 'ALL' AS PLT_CD
, ORG_CD AS DEPT_CD
, ORG_NM AS DEPT_NM
, NULL AS DEPT_EN_NM
, ISNULL(UP_ORG_CD, 'ROOT') AS UP_DEPT_CD
, 'Y' AS USE_YN
, NULL AS REM
, NULL AS STS
, 'SYSTEM' AS REG_ID
, DBO.GET_DATETIME('YYYYMMDDHHMISS') AS REG_DT
, 'SYSTEM' AS MOD_ID
, DBO.GET_DATETIME('YYYYMMDDHHMISS') AS MOD_DT
FROM [HR]..EHR_LN.V_SRM_ORG) SURC
ON (TRGT.SYS_ID = SURC.SYS_ID
AND TRGT.COMP_CD = SURC.COMP_CD
AND TRGT.PLT_CD = SURC.PLT_CD
AND TRGT.DEPT_CD = SURC.DEPT_CD)
WHEN MATCHED THEN
UPDATE SET DEPT_NM = SURC.DEPT_NM
, UP_DEPT_CD = SURC.UP_DEPT_CD
, STS = SURC.STS
, MOD_ID = SURC.MOD_ID
, MOD_DT = SURC.MOD_DT
WHEN NOT MATCHED THEN
INSERT VALUES ( SURC.SYS_ID
, SURC.COMP_CD
, SURC.PLT_CD
, SURC.DEPT_CD
, SURC.DEPT_NM
, SURC.DEPT_EN_NM
, SURC.UP_DEPT_CD
, SURC.USE_YN
, SURC.REM
, SURC.STS
, SURC.REG_ID
, SURC.REG_DT
, SURC.MOD_ID
, SURC.MOD_DT)
WHEN NOT MATCHED BY SOURCE THEN
UPDATE SET USE_YN = 'N'
, STS = 'D'
, MOD_ID = DBO.GET_DATETIME('YYYYMMDDHHMISS')
, MOD_DT = DBO.GET_DATETIME('YYYYMMDDHHMISS');
MERGE INTO 사용예시
|
2012.07.19 16:22:12
|
2012.07.19 16:22:12
|
533
|
Aiden
Total of Attached file
0.00 Bytes of 0 files
2017.10.23
2012.10.29
2012.10.19
2012.09.04
2012.09.01
2012.07.19
2012.07.18
2012.07.10
2012.07.05
2012.07.03
2012.07.02