sqlserver相关 游标实现增量数据等

USE [BI_DW]
GO
/****** Object: StoredProcedure [dbo].[SP_SL_TM_REGION] Script Date: 2021/6/3 17:50:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

– Batch submitted through debugger: SQLQuery1.sql|7|0|C:\Users\la\AppData\Local\Temp\2~vsC4D.sql
ALTER PROCEDURE [dbo].[SP_SL_TM_REGION]
AS
BEGIN
DECLARE @JOB_ID INT=1019;
DECLARE @V_RESULT INT;
DECLARE @JOB_DT_ID INT;
DECLARE @JOB_TYPE INT;
DECLARE @EXEC_FLG INT;
DECLARE @EXEC_STEPS INT;
DECLARE @START_DATE DATE;
DECLARE @END_DATE DATE;

DECLARE @ERROR_NUMBER INT;
DECLARE @ERROR_SEVERITY INT;
DECLARE @ERROR_STATE INT;
DECLARE @ERROR_LINE INT;
DECLARE @ERROR_MESSAGE VARCHAR(MAX);

–步骤一：生成本次JOB运行的时间戳，插入表[dbo].[ETL_DT_LOG_INFO]
–执行时间戳SP,返回执行结果值,0:时间戳生成失败；1:时间戳生成成功
EXEC [dbo].[SP_ETL_DT_LOG_INFO_INSERT] @JOB_ID,@V_RESULT OUTPUT;

IF @V_RESULT=0
BEGIN
RETURN;
END
ELSE
BEGIN
–获取JOB是全量还是增量加载,时间戳是变量OR常量,本次从第几步开始执行
SELECT @JOB_TYPE=JOB_TYPE,@EXEC_FLG=EXEC_FLG,@EXEC_STEPS=EXEC_STEPS FROM [dbo].[ETL_JOB_INFO] WHERE [JOB_ID]=@JOB_ID;

--初始化JOB执行时间戳记录
EXEC [dbo].[SP_ETL_DT_LOG_INFO_UPDATE_RUN] @JOB_ID;
--获取本次JOB执行时间戳ID
SELECT @JOB_DT_ID=MAX(JOB_DT_ID) FROM [dbo].[ETL_DT_LOG_INFO] WHERE JOB_ID=@JOB_ID;

--执行步骤1
IF @EXEC_STEPS <= 1
BEGIN
INSERT INTO [dbo].[ETL_JOB_LOG_INFO]
VALUES(@JOB_ID,'1','生成本次JOB执行时间戳记录',@JOB_DT_ID,NULL,GETDATE());
END;

END

SET NOCOUNT ON
BEGIN TRY
BEGIN TRAN
–执行步骤2
IF @EXEC_STEPS <= 2
BEGIN
–清空中间表
TRUNCATE TABLE ODS_SL_TM_REGION;

    --获取JOOB执行的开始日期，结束日期
    SELECT @START_DATE=START_DATE,@END_DATE=END_DATE FROM [dbo].[ETL_DT_LOG_INFO] WHERE JOB_DT_ID=@JOB_DT_ID;

    --JOB_TYPE:1全量，2增量
    --同步DW数据至ODS中间表
    IF @JOB_TYPE=1
    BEGIN
    INSERT INTO [dbo].ODS_SL_TM_REGION
    SELECT [REGION_ID]
          ,[REGION_CODE]
          ,[REGION_NAME]
          ,[PARENT_ID]
          ,[REGION_TYPE]
          ,[ZIP_CODE]
          ,[STATUS]
          ,[TREE_CODE]
          ,[CREATE_DATE]
          ,[CREATE_BY]
          ,[UPDATE_DATE]
          ,[UPDATE_BY]
          ,[LMS_ID]
          ,[SWT_ID]
          ,[DB_CREATE_DATE]
          ,[DB_UPDATE_DATE]
          ,[F_IMP_NUMBER]
          ,[S_IMP_NUMBER]
          ,[DW_CREATED_USER_NAME]
          ,[DW_CREATED_TIME]
          ,[DW_UPDATED_USER_NAME]
          ,[DW_UPDATED_TIME]
          ,[DW_DEL_FLAG]
          ,GETDATE()
    FROM OPENQUERY(MYSQLCONN,'SELECT * FROM ods.dm_tm_region_dcs');

    INSERT INTO [dbo].[ETL_JOB_LOG_INFO]
    VALUES(@JOB_ID,'2','JOB_ID:'+CAST(@JOB_ID AS VARCHAR)+',全量同步DW数据至ODS中间表',@JOB_DT_ID,NULL,GETDATE());
    END
    ELSE
    BEGIN
    DECLARE @SQL_STR VARCHAR(MAX)
    SET @SQL_STR='SELECT [REGION_ID]
          ,[REGION_CODE]
          ,[REGION_NAME]
          ,[PARENT_ID]
          ,[REGION_TYPE]
          ,[ZIP_CODE]
          ,[STATUS]
          ,[TREE_CODE]
          ,[CREATE_DATE]
          ,[CREATE_BY]
          ,[UPDATE_DATE]
          ,[UPDATE_BY]
          ,[LMS_ID]
          ,[SWT_ID]
          ,[DB_CREATE_DATE]
          ,[DB_UPDATE_DATE]
          ,[F_IMP_NUMBER]
          ,[S_IMP_NUMBER]
          ,[DW_CREATED_USER_NAME]
          ,[DW_CREATED_TIME]
          ,[DW_UPDATED_USER_NAME]
          ,[DW_UPDATED_TIME]
          ,[DW_DEL_FLAG]
          ,GETDATE()
    FROM OPENQUERY