SELECT 'Rows updated:',COUNT(1) FROM (SELECT 1 FROM DELTA.PRMCN WHERE ETL_FLAG IN ('A','D')) S;--重跑：删除已跑入数据DELETE FROM CCRD.PRMCN WHERE JOB_SEQ_ID= New_JOB_SEQ_ID;--重跑：从历史表恢复数据INSERT INTO CCRD.PRMCN(CATEGORY,CATE_DESC,CURR_NUM2,PROC_DAYS,BRANCH,PDCAT_FLAG,EFF_DT,END_DT,JOB_SEQ_ID)select CATEGORY,CATE_DESC,CURR_NUM2,PROC_DAYS,BRANCH,PDCAT_FLAG,EFF_DT,END_DT,JOB_SEQ_IDfrom ODSHIS.PRMCN WHERE NEW_JOB_SEQ_ID= New_JOB_SEQ_ID;--重跑：删除已跑入历史数据DELETE FROM ODSHIS.PRMCN WHERE NEW_JOB_SEQ_ID= New_JOB_SEQ_ID;--备份数据到历史表SELECT 'Rows readed:',COUNT(1),'Rows changed:',COUNT(1) FROM (SELECT 1 FROM DELTA.PRMCN WHERE ETL_FLAG IN ('I','A','D')) S;SELECT 'Rows updated:',COUNT(1) FROM NEW TABLE (INSERT INTO ODSHIS.PRMCN(CATEGORY,CATE_DESC,CURR_NUM2,PROC_DAYS,BRANCH,PDCAT_FLAG,EFF_DT,END_DT,JOB_SEQ_ID,NEW_JOB_SEQ_ID)select CATEGORY,CATE_DESC,CURR_NUM2,PROC_DAYS,BRANCH,PDCAT_FLAG,EFF_DT,END_DT,JOB_SEQ_ID,New_JOB_SEQ_IDfrom CCRD.PRMCN TWHERE T.END_DT='9999-12-31' AND EXISTS ( SELECT 1 FROM DELTA.PRMCN SWHERE T.CATEGORY=S.CATEGORY ));--断链MERGE INTO CCRD.PRMCN T USING (SELECT * FROM DELTA.PRMCN WHERE ETL_FLAG IN ('I','D','A')) SON T.CATEGORY=S.CATEGORY  AND T.END_DT='9999-12-31'WHEN MATCHED THEN UPDATE SET T.END_DT='#DATEOFDATA#', T.JOB_SEQ_ID= New_JOB_SEQ_ID;--加链INSERT INTO CCRD.PRMCN(CATEGORY,CATE_DESC,CURR_NUM2,PROC_DAYS,BRANCH,PDCAT_FLAG,EFF_DT,END_DT,JOB_SEQ_ID)select CATEGORY,CATE_DESC,CURR_NUM2,PROC_DAYS,BRANCH,PDCAT_FLAG,'#DATEOFDATA#','9999-12-31',New_JOB_SEQ_IDfrom DELTA.PRMCN where ETL_FLAG in ('A','I');--保持数据完整性MERGE INTO CCRD.PRMCN T USING (SELECT * FROM DELTA.PRMCN WHERE ETL_FLAG = 'D' ) SON T.CATEGORY=S.CATEGORY WHEN NOT MATCHED THEN INSERT (CATEGORY,CATE_DESC,CURR_NUM2,PROC_DAYS,BRANCH,PDCAT_FLAG,EFF_DT,END_DT,JOB_SEQ_ID)VALUES (CATEGORY,CATE_DESC,CURR_NUM2,PROC_DAYS,BRANCH,PDCAT_FLAG,'#DATEOFDATA#','#DATEOFDATA#',New_JOB_SEQ_ID);

以上就是对数据进行做拉链加载