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:    <接口請(qǐng)求記錄表GLPS_APIREQUEST數(shù)據(jù)遷移>
-- =============================================
ALTER PROCEDURE [dbo].[TY_SP_ApiRequestToHis]
    -- Add the parameters for the stored procedure here
    @PreCountN int = 2000     --每次執(zhí)行的條數(shù)N
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    declare @tableDataCount int;          --遷移前的數(shù)據(jù)條數(shù)
    declare @tableDataCountHis int;       --遷移前歷史數(shù)據(jù)庫的數(shù)據(jù)條數(shù)
    declare @tableDataCount2 int;         --遷移后的數(shù)據(jù)條數(shù)
    declare @tableDataCount2His int;      --遷移后歷史數(shù)據(jù)庫的數(shù)據(jù)條數(shù)
    declare @maxCreateTime datetime;      --取N條數(shù)據(jù)中最大的創(chuàng)建時(shí)間
    declare @maxCreateTimeHis datetime;   --歷史數(shù)據(jù)庫中的最大創(chuàng)建時(shí)間
    declare @beginTime datetime;          --開始執(zhí)行時(shí)間
    declare @endTime datetime;            --執(zhí)行完成時(shí)間
    declare @execTimeMS int;              --執(zhí)行時(shí)間（毫秒數(shù)）

    --中間步驟debugger耗時(shí)使用
    declare @tmpBeginTime datetime;       --(臨時(shí))開始執(zhí)行時(shí)間
    declare @tmpEndTime datetime;         --(臨時(shí))執(zhí)行完成時(shí)間
    declare @tmpExecTimeMS int;           --(臨時(shí))執(zhí)行時(shí)間（毫秒數(shù)）

    select @beginTime = getdate();

    --遷移前：先查詢數(shù)據(jù)條數(shù)
    select @tableDataCount = count(1) from [Tyingsoft.GLPS].dbo.GLPS_APIREQUEST;
    select @tableDataCountHis =count(1) from  [Tyingsoft.GLPS_His].dbo.GLPS_APIREQUEST;

    print '【接口請(qǐng)求記錄表（GLPS_APIREQUEST）數(shù)據(jù)遷移】開始執(zhí)行時(shí)間：' + convert(nvarchar(50),@beginTime,20);
    print '本次計(jì)劃遷移數(shù)據(jù)條數(shù)：'+ cast( @PreCountN as nvarchar(20));

    --創(chuàng)建一個(gè)臨時(shí)公用表達(dá)式 （表中最早創(chuàng)建的N條數(shù)據(jù)）
    with topNRecord (FCREATETIME)
    as
    (
        select top (@PreCountN) FCREATETIME from GLPS_APIREQUEST  order by FCREATETIME
    )

    --取N條數(shù)據(jù)中最大的創(chuàng)建時(shí)間
    select @maxCreateTime =max(FCREATETIME) from topNRecord

    print '對(duì)應(yīng)遷移數(shù)據(jù)FCREATETIME為：'+ convert(nvarchar(50),@maxCreateTime,21); --日期轉(zhuǎn)化為字符串格式：yyyy-MM-dd HH:mm:ss.fff


    select @tmpBeginTime = GETDATE();  --中間步驟開始計(jì)時(shí)

    --第一步：將N條數(shù)數(shù)據(jù)寫入到歷史數(shù)據(jù)庫
    insert into [Tyingsoft.GLPS_His].dbo.GLPS_APIREQUEST
    select * from [Tyingsoft.GLPS].dbo.GLPS_APIREQUEST  where  FCREATETIME <=@maxCreateTime

    select @tmpEndTime = GETDATE();   --中間步驟計(jì)時(shí)結(jié)束
    print '數(shù)據(jù)遷移，插入耗時(shí)（毫秒）：' +cast( datediff(millisecond,@tmpBeginTime,@tmpEndTime) as nvarchar(20));


    --第二步：對(duì)比歷史數(shù)據(jù)庫的數(shù)據(jù)
    select @maxCreateTimeHis=max(FCREATETIME) from [Tyingsoft.GLPS_His].dbo.GLPS_APIREQUEST

    if @maxCreateTime = @maxCreateTimeHis
       begin
            select @tmpBeginTime = GETDATE();  --中間步驟開始計(jì)時(shí)

            --第三步：執(zhí)行完以后，再刪除數(shù)據(jù)
            delete from GLPS_APIREQUEST where FCREATETIME <=@maxCreateTime
            print '遷移后刪除數(shù)據(jù)條數(shù)：' + cast( @@ROWCOUNT as nvarchar(50));

            select @tmpEndTime = GETDATE();       --中間步驟計(jì)時(shí)結(jié)束
            print '數(shù)據(jù)遷移，刪除耗時(shí)（毫秒）：' +cast( datediff(millisecond,@tmpBeginTime,@tmpEndTime) as nvarchar(20));
        end
    else
        print '遷移后，日期校驗(yàn)錯(cuò)誤，未刪除數(shù)據(jù)?。?！'

    --遷移后：再查詢數(shù)據(jù)條數(shù)
    select @tableDataCount2 = count(1) from [Tyingsoft.GLPS].dbo.GLPS_APIREQUEST;
    select @tableDataCount2His =count(1) from  [Tyingsoft.GLPS_His].dbo.GLPS_APIREQUEST;

    print '遷移前GLPS_APIREQUEST的數(shù)據(jù)條數(shù)：' + cast(@tableDataCount as nvarchar(20))
     + ',遷移后數(shù)據(jù)條數(shù)：'+cast(@tableDataCount2 as nvarchar(20))
     + ',差額：'+cast((@tableDataCount2-@tableDataCount) as nvarchar(20));

    print '遷移前His.GLPS_APIREQUEST的數(shù)據(jù)條數(shù)：'
    + cast(@tableDataCountHis as nvarchar(20))
    +',遷移后數(shù)據(jù)條數(shù)：'+cast(@tableDataCount2His as nvarchar(20))
    + ',差額：'+cast((@tableDataCount2His-@tableDataCountHis) as nvarchar(20));

    print '注意：正式環(huán)境一直有數(shù)據(jù)變動(dòng)，所以會(huì)有一定的偏差！';


    select @endTime = GETDATE();
    print '總耗時(shí)（毫秒）：' +cast( datediff(millisecond,@beginTime,@endTime) as nvarchar(20));


END