SQL SERVERのストアドプロシージャを使用した履歴データの移行について

USE [Tyingsoft.GLPS]
GO
/****** Object: StoredProcedure [dbo]. [TY_SP_ApiRequestToHis] Script Date: 2021-09-16 15:35:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Last Edit date:
-- Description: <Interface request log GLPS_APIREQUEST data migration>
-- =============================================
ALTER PROCEDURE [dbo]. [TY_SP_ApiRequestToHis]
    -- Add the parameters for the stored procedure here
    @PreCountN int = 2000 -- Number of entries per execution N
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    declare @tableDataCount int; -- Number of data items before migration
    declare @tableDataCountHis int; -- the number of data items in the historical database before migration
    declare @tableDataCount2 int; -- Number of data items after migration
    declare @tableDataCount2His int; -- the number of data items in the historical database after migration
    declare @maxCreateTime datetime; -- take the maximum creation time of the N data items
    declare @maxCreateTimeHis datetime; -- the maximum creation time in the historical database
    declare @beginTime datetime; -- start time
    declare @endTime datetime; --execution completion time
    declare @execTimeMS int; --execution time (in milliseconds)

    -- intermediate steps debugger time consuming use
    declare @tmpBeginTime datetime; --(temporary) start time of execution
    declare @tmpEndTime datetime; --(temporary) execution completion time
    declare @tmpExecTimeMS int; --(temporary) execution time (in milliseconds)

    select @beginTime = getdate();

    -- before migration: query the number of data entries first
    select @tableDataCount = count(1) from [Tyingsoft.GLPS].dbo.GLPS_APIREQUEST;
    select @tableDataCountHis =count(1) from [Tyingsoft.GLPS_His].dbo.GLPS_APIREQUEST;

    print '[Interface request record table (GLPS_APIREQUEST) data migration] start time:' + convert(nvarchar(50),@beginTime,20);
    print 'Number of data entries scheduled to be migrated this time: ' + cast( @PreCountN as nvarchar(20));

    --create a temporary common expression (the earliest N entries created in the table)
    with topNRecord (FCREATETIME)
    as
    (
        select top (@PreCountN) FCREATETIME from GLPS_APIREQUEST order by FCREATETIME
    )

    --get the maximum creation time in N data
    select @maxCreateTime =max(FCREATETIME) from topNRecord

    print 'The corresponding migration data FCREATETIME is:' + convert(nvarchar(50),@maxCreateTime,21); -- Convert the date to string format: yyyy-MM-dd HH:mm:ss.fff


    select @tmpBeginTime = GETDATE(); --Intermediate step to start timing

    -- Step 1: Write N count data to the history database
    insert into [Tyingsoft.GLPS_His].dbo.GLPS_APIREQUEST
    select * from [Tyingsoft.GLPS].dbo.GLPS_APIREQUEST where FCREATETIME <=@maxCreateTime

    select @tmpEndTime = GETDATE(); -- intermediate step timed out
    print 'Data migration, insert elapsed time (milliseconds):' +cast( datediff(millisecond,@tmpBeginTime,@tmpEndTime) as nvarchar(20));


    -- Step 2: Compare data from historical database
    select @maxCreateTimeHis=max(FCREATETIME) from [Tyingsoft.GLPS_His].dbo.GLPS_APIREQUEST

    if @maxCreateTime = @maxCreateTimeHis
       begin
            select @tmpBeginTime = GETDATE(); --Intermediate step starts timing

            -- Step 3: After execution, delete the data
            delete from GLPS_APIREQUEST where FCREATETIME <=@maxCreateTime
            print 'Number of data entries deleted after migration:' + cast( @@ROWCOUNT as nvarchar(50));

            select @tmpEndTime = GETDATE(); -- intermediate step timed out
            print 'Data migration, deletion elapsed time (milliseconds):' + cast( datediff(millisecond,@tmpBeginTime,@tmpEndTime) as nvarchar(20));
        end
    else
        print 'After migration, date checksum error, data not deleted!!!'

    -- After migration: query data count again
    select @tableDataCount2 = count(1) from [Tyingsoft.GLPS].dbo.GLPS_APIREQUEST;
    select @tableDataCount2His =count(1) from [Tyingsoft.GLPS_His].dbo.GLPS_APIREQUEST;

    print 'Number of data items in GLPS_APIREQUEST before migration:' + cast(@tableDataCount as nvarchar(20))
     + ',Number of data entries after migration:' + cast(@tableDataCount2 as nvarchar(20))
     + ',Difference: ' + cast((@tableDataCount2-@tableDataCount) as nvarchar(20));

    print 'Number of data entries in His.GLPS_APIREQUEST before migration:'
    + cast(@tableDataCountHis as nvarchar(20))
    + ',Number of data entries after migration:' + cast(@tableDataCount2His as nvarchar(20))
    + ',Difference: '+cast((@tableDataCount2His-@tableDataCountHis) as nvarchar(20));

    print 'Note: The formal environment has always had data changes, so there will be some deviation!' ;


    select @endTime = GETDATE();
    print 'Total elapsed time (milliseconds):' +cast( datediff(millisecond,@beginTime,@endTime) as nvarchar(20));


END