如何使用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: <接口请求记录表GLPS_APIREQUEST数据迁移>
— =============================================
ALTER PROCEDURE [dbo].[TY_SP_ApiRequestToHis]
— Add the parameters for the stored procedure here
@PreCountN int = 2000 –每次执行的条数N
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;

declare @tableDataCount int; –迁移前的数据条数
declare @tableDataCountHis int; –迁移前历史数据库的数据条数
declare @tableDataCount2 int; –迁移后的数据条数
declare @tableDataCount2His int; –迁移后历史数据库的数据条数
declare @maxCreateTime datetime; –取N条数据中最大的创建时间
declare @maxCreateTimeHis datetime; –历史数据库中的最大创建时间
declare @beginTime datetime; –开始执行时间
declare @endTime datetime; –执行完成时间
declare @execTimeMS int; –执行时间（毫秒数）

–中间步骤debugger耗时使用
declare @tmpBeginTime datetime; –(临时)开始执行时间
declare @tmpEndTime datetime; –(临时)执行完成时间
declare @tmpExecTimeMS int; –(临时)执行时间（毫秒数）

select @beginTime = getdate();

–迁移前：先查询数据条数
select @tableDataCount = count(1) from [Tyingsoft.GLPS].dbo.GLPS_APIREQUEST;
select @tableDataCountHis =count(1) from [Tyingsoft.GLPS_His].dbo.GLPS_APIREQUEST;

print ‘【接口请求记录表（GLPS_APIREQUEST）数据迁移】开始执行时间：’ + convert(nvarchar(50),@beginTime,20);
print ‘本次计划迁移数据条数：’+ cast( @PreCountN as nvarchar(20));

–创建一个临时公用表达式 （表中最早创建的N条数据）
with topNRecord (FCREATETIME)
as
(
select top (@PreCountN) FCREATETIME from GLPS_APIREQUEST order by FCREATETIME
)

–取N条数据中最大的创建时间
select @maxCreateTime =max(FCREATETIME) from topNRecord

print ‘对应迁移数据FCREATETIME为：’+ convert(nvarchar(50),@maxCreateTime,21); –日期转化为字符串格式：yyyy-MM-dd HH:mm:ss.fff

select @tmpBeginTime = GETDATE(); –中间步骤开始计时

–第一步：将N条数数据写入到历史数据库
insert into [Tyingsoft.GLPS_His].dbo.GLPS_APIREQUEST
select * from [Tyingsoft.GLPS].dbo.GLPS_APIREQUEST where FCREATETIME <=@maxCreateTime

select @tmpEndTime = GETDATE(); –中间步骤计时结束
print ‘数据迁移，插入耗时（毫秒）：’ +cast( datediff(millisecond,@tmpBeginTime,@tmpEndTime) as nvarchar(20));

–第二步：对比历史数据库的数据
select @maxCreateTimeHis=max(FCREATETIME) from [Tyingsoft.GLPS_His].dbo.GLPS_APIREQUEST

if @maxCreateTime = @maxCreateTimeHis
begin
select @tmpBeginTime = GETDATE(); –中间步骤开始计时

–第三步：执行完以后，再删除数据
delete from GLPS_APIREQUEST where FCREATETIME <=@maxCreateTime
print ‘迁移后删除数据条数：’ + cast( @@ROWCOUNT as nvarchar(50));

select @tmpEndTime = GETDATE(); –中间步骤计时结束
print ‘数据迁移，删除耗时（毫秒）：’ +cast( datediff(millisecond,@tmpBeginTime,@tmpEndTime) as nvarchar(20));
end
else
print ‘迁移后，日期校验错误，未删除数据！！！’

–迁移后：再查询数据条数
select @tableDataCount2 = count(1) from [Tyingsoft.GLPS].dbo.GLPS_APIREQUEST;
select @tableDataCount2His =count(1) from [Tyingsoft.GLPS_His].dbo.GLPS_APIREQUEST;

print ‘迁移前GLPS_APIREQUEST的数据条数：’ + cast(@tableDataCount as nvarchar(20))
+ ‘,迁移后数据条数：’+cast(@tableDataCount2 as nvarchar(20))
+ ‘,差额：’+cast((@tableDataCount2-@tableDataCount) as nvarchar(20));

print ‘迁移前His.GLPS_APIREQUEST的数据条数：’
+ cast(@tableDataCountHis as nvarchar(20))
+’,迁移后数据条数：’+cast(@tableDataCount2His as nvarchar(20))
+ ‘,差额：’+cast((@tableDataCount2His-@tableDataCountHis) as nvarchar(20));

print ‘注意：正式环境一直有数据变动，所以会有一定的偏差！’;

select @endTime = GETDATE();
print ‘总耗时（毫秒）：’ +cast( datediff(millisecond,@beginTime,@endTime) as nvarchar(20));

END