Untitled

SET IDENTITY_INSERT DWH_DEV_LAND.dbo.XLNT_EXU_FLEXBHISTORYLINEAUTO ON;

DECLARE @StartDate DATE = '2020-03-13';
DECLARE @EndDate DATE = DATEADD(DAY, 1, @StartDate);
DECLARE @CurrentDate DATE = GETDATE();

-- Log the start of the process
PRINT 'Process started on ' + CONVERT(VARCHAR, @CurrentDate, 120);

WHILE @EndDate <= @CurrentDate
BEGIN
    DECLARE @StartDateStr VARCHAR(10) = CONVERT(VARCHAR, @StartDate, 120);
    DECLARE @EndDateStr VARCHAR(10) = CONVERT(VARCHAR, @EndDate, 120);

	-- Log the current date being processed
    PRINT 'Processing data for date: ' + @StartDateStr;

    DECLARE @DynamicSQL NVARCHAR(MAX) = N'
        INSERT INTO DWH_DEV_LAND.dbo.XLNT_EXU_FLEXBHISTORYLINEAUTO (
      DATAAREAID
      ,HISTKEYNUMBER
      ,DATETIME24HOUR
      ,AMRLIMITFROM
      ,RECID
      ,DEL_TIMEDATE
      ,DEL_TIME24HOUR
      ,AMRLIMITTO
      ,TIMEVALUE
      ,AMRMARKETPRICEMARK
      ,AMRPRICE
      ,AMRCHARGEDEDUCTIONTYPE
      ,AMRCHARGEDEDUCTION
      ,POWEREXCHANGEPRICE
      ,CALCULATEDPRICE
      ,AMRBLOCKTYPE
      ,AMRLIMITFROMDATE
      ,AMRLIMITTODATE
      ,RECVERSION
      ,DATETIME24HOURTZID
      ,PRICEAREANUM
      ,BILLINGLOGNUM
      ,DW_Version
      ,DW_Operation
      ,DW_InsertDateTime
      ,DW_Rowid
        )
        SELECT
      DATAAREAID
      ,HISTKEYNUMBER
      ,DATETIME24HOUR
      ,AMRLIMITFROM
      ,RECID
      ,DEL_TIMEDATE
      ,DEL_TIME24HOUR
      ,AMRLIMITTO
      ,TIMEVALUE
      ,AMRMARKETPRICEMARK
      ,AMRPRICE
      ,AMRCHARGEDEDUCTIONTYPE
      ,AMRCHARGEDEDUCTION
      ,POWEREXCHANGEPRICE
      ,CALCULATEDPRICE
      ,AMRBLOCKTYPE
      ,AMRLIMITFROMDATE
      ,AMRLIMITTODATE
      ,RECVERSION
      ,DATETIME24HOURTZID
      ,PRICEAREANUM
      ,BILLINGLOGNUM
      ,DW_Version
      ,DW_Operation
      ,DW_InsertDateTime
      ,DW_Rowid
        FROM OPENQUERY (SNOWFLAKE, 
            ''SELECT 
      DATAAREAID
      ,HISTKEYNUMBER
      ,DATETIME24HOUR
      ,AMRLIMITFROM
      ,RECID
      ,DEL_TIMEDATE
      ,DEL_TIME24HOUR
      ,AMRLIMITTO
      ,TIMEVALUE
      ,AMRMARKETPRICEMARK
      ,AMRPRICE
      ,AMRCHARGEDEDUCTIONTYPE
      ,AMRCHARGEDEDUCTION
      ,POWEREXCHANGEPRICE
      ,CALCULATEDPRICE
      ,AMRBLOCKTYPE
      ,AMRLIMITFROMDATE
      ,AMRLIMITTODATE
      ,RECVERSION
      ,DATETIME24HOURTZID
      ,PRICEAREANUM
      ,BILLINGLOGNUM
      ,DW_Version
      ,DW_Operation
      ,DW_InsertDateTime
      ,DW_Rowid 
            FROM DWH_PROD.LAND.XLNT_EXU_FLEXBHISTORYLINE 
            WHERE DW_INSERTDATETIME >= ''''' + @StartDateStr + ''''' AND DW_INSERTDATETIME < ''''' + @EndDateStr + ''''''')';

    EXEC sp_executesql @DynamicSQL;

    SET @StartDate = DATEADD(DAY, 1, @StartDate);
    SET @EndDate = DATEADD(DAY, 1, @EndDate);
END;

-- Log the completion of the process
PRINT 'Process completed on ' + CONVERT(VARCHAR, @CurrentDate, 120);

SET IDENTITY_INSERT DWH_DEV_LAND.dbo.XLNT_EXU_FLEXBHISTORYLINEAUTO OFF;