SQL Server data patching模板

DECLARE @SVOC_HIS_FULL_TBL_NM   NVARCHAR(128);
DECLARE @SVOC_TBL_OWNER         NVARCHAR(10);
DECLARE @SVOC_CUR_FULL_TBL_NM   NVARCHAR(128);
DECLARE @RERUN_DT               NVARCHAR(10);
DECLARE @NEW_EFF_DT             NVARCHAR(100);
DECLARE @JOIN_STMT              NVARCHAR(2000);
DECLARE @UPD_SET_VALUE_STMT     NVARCHAR(MAX);
DECLARE @NON_ETL_FIELD_HIS_STR  NVARCHAR(MAX);
DECLARE @NON_ETL_FIELD_STR      NVARCHAR(MAX);
DECLARE @V_SQL                  VARCHAR(MAX);

select @RERUN_DT='2020-09-02'       --delete the date after this day(include 2020-09-15)
select @NEW_EFF_DT='9999-12-31'
select @SVOC_HIS_FULL_TBL_NM='AA_TBLAGTPACKAGEHIST_HIS'    --full table name
select @SVOC_TBL_OWNER='dna_svoa'   --table owner

BEGIN TRANSACTION RERUN_TRAN;
  SELECT @SVOC_CUR_FULL_TBL_NM =
         SUBSTRING(OBJECT_NAME(IDX_COL.OBJECT_ID),1,LEN(OBJECT_NAME(IDX_COL.OBJECT_ID)) - 4),
         @JOIN_STMT =
         CONVERT(TEXT,STRING_AGG(CONVERT(NVARCHAR(MAX),'SOURCE.' + QUOTENAME(C.NAME) +'=TARGET.' + QUOTENAME(C.NAME)),' AND ') WITHIN GROUP(ORDER BY KEY_ORDINAL))
    FROM SYS.INDEXES IDX
    JOIN SYS.INDEX_COLUMNS IDX_COL
      ON IDX.OBJECT_ID = IDX_COL.OBJECT_ID
     AND IDX.INDEX_ID = IDX_COL.INDEX_ID
     AND IDX.IS_PRIMARY_KEY = 1
     AND OBJECT_SCHEMA_NAME(IDX.OBJECT_ID) = @SVOC_TBL_OWNER
    JOIN SYS.COLUMNS C
      ON C.OBJECT_ID = IDX_COL.OBJECT_ID
     AND C.COLUMN_ID = IDX_COL.COLUMN_ID
   WHERE KEY_ORDINAL > 0
     AND C.NAME <> 'EFF_ST_DT'
     AND OBJECT_NAME(IDX_COL.OBJECT_ID) = @SVOC_HIS_FULL_TBL_NM
   GROUP BY IDX_COL.OBJECT_ID, IDX_COL.INDEX_ID;


SELECT @UPD_SET_VALUE_STMT =
       CONVERT(TEXT,
               STRING_AGG(CONVERT(NVARCHAR(MAX),
                                  QUOTENAME(C.NAME) + '=SOURCE.' +
                                  QUOTENAME(C.NAME)),
                          ',
     ') WITHIN GROUP(ORDER BY COLUMN_ID))
  from SYS.COLUMNS C
 where OBJECT_NAME(C.OBJECT_ID) = @SVOC_CUR_FULL_TBL_NM
   and C.COLUMN_ID not in
       (
        
        select IDX_COL.COLUMN_ID
          FROM SYS.INDEXES IDX
          JOIN SYS.INDEX_COLUMNS IDX_COL
            ON IDX.OBJECT_ID = IDX_COL.OBJECT_ID
           AND IDX.INDEX_ID = IDX_COL.INDEX_ID
           AND IDX.IS_PRIMARY_KEY = 1
           AND OBJECT_SCHEMA_NAME(IDX.OBJECT_ID) = @SVOC_TBL_OWNER
         where OBJECT_NAME(IDX_COL.OBJECT_ID) = @SVOC_CUR_FULL_TBL_NM);

SELECT @NON_ETL_FIELD_STR =
       CONVERT(TEXT,
               STRING_AGG(CONVERT(NVARCHAR(MAX), QUOTENAME(C.NAME)), ',')
               WITHIN GROUP(ORDER BY COLUMN_ID)),
       
       @NON_ETL_FIELD_HIS_STR =
       CONVERT(TEXT,
               STRING_AGG(CONVERT(NVARCHAR(MAX), QUOTENAME(C.NAME)),
                          ',SOURCE.') WITHIN GROUP(ORDER BY COLUMN_ID))
  from SYS.COLUMNS C
 where OBJECT_NAME(C.OBJECT_ID) = @SVOC_CUR_FULL_TBL_NM;

    
select @V_SQL='BEGIN TRANSACTION RERUN_TRAN;

DELETE FROM '+@SVOC_TBL_OWNER+'.'+@SVOC_HIS_FULL_TBL_NM + ' WHERE EFF_ST_DT&gt;='''+ @RERUN_DT+''';'+'

UPDATE '+@SVOC_TBL_OWNER+'.'+@SVOC_HIS_FULL_TBL_NM +'
   SET IS_ACTIVE=1,EFF_END_DT='''+@NEW_EFF_DT+''' 
 WHERE EFF_END_DT&gt;='''+@RERUN_DT+''';

MERGE '+@SVOC_TBL_OWNER+'.'+@SVOC_CUR_FULL_TBL_NM+' AS TARGET
USING (
    SELECT *
    FROM '+@SVOC_TBL_OWNER+'.'+@SVOC_HIS_FULL_TBL_NM+'
    WHERE IS_ACTIVE = 1
) AS SOURCE
ON ( '+@JOIN_STMT+' )
WHEN MATCHED THEN
    UPDATE SET
     '+@UPD_SET_VALUE_STMT+'
WHEN NOT MATCHED BY TARGET THEN
    INSERT ('+@NON_ETL_FIELD_STR+')
    VALUES (SOURCE.'+@NON_ETL_FIELD_HIS_STR+')
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
\;

COMMIT TRANSACTION RERUN_TRAN;

UPDATE DNA_ETL.DNA_LOG
   SET STATUS=''READY''
 WHERE BAT_END_TS &gt; '''+@RERUN_DT+' 03:00:00.000''
   AND JOB_NM LIKE ''%'+@SVOC_CUR_FULL_TBL_NM+''';
'

print @V_SQL;

COMMIT TRANSACTION RERUN_TRAN;