将表中的数据保留一周的数据其余全部转移
用于数据库迁移。
code:
USE Kyt
GO
/* Daily_StockSettlement 保留最近一周数据,其他全部迁移至历史数据库表(KYT_History.dbo.Daily_StockSettlement_his) */
-- 此过程适合sqlserv 2005 或2005以上的版本
-- SELECT TOP 100 DSS_CreateDate FROM Daily_StockSettlement ORDER BY DSS_CreateDate ASC
-- SELECT TOP 100 DSS_CreateDate FROM KYT_History.dbo.Daily_StockSettlement_his ORDER BY DSS_CreateDate ASC
-- SELECT count(1) FROM Daily_StockSettlement
DECLARE @mindate DATETIME,@maxdate DATETIME,@maxday INT,@maxpage INT,@minday INT
SET @maxday = 1 --每次操作【*】天的数据 如1天,当每天的数量太大时,建议减小此值(现在数据中一天约20000条数据)
SET @minday = 9 --保留最近 【*】 天的数据 如 9天
--获取当前表数据的最前一条(order by 创建时间)
SELECT TOP 1 @mindate = DSS_CreateDate FROM Daily_StockSettlement ORDER BY DSS_CreateDate ASC
--获取当前表数据的最后一条(order by 创建时间)
SELECT TOP 1 @maxdate = DSS_CreateDate FROM Daily_StockSettlement ORDER BY DSS_CreateDate DESC
--设置循环的次数
SET @maxpage = ((DATEDIFF(DAY,@mindate,@maxdate))-@minday)/@maxday
PRINT '共需要执行 '+ convert(varchar(10), @maxpage)+' 次循环。'
-- 开始循环数据
WHILE (@maxpage >= 0)
BEGIN
PRINT 'NO: '+convert(varchar(10), @maxpage)+' 次循环 开始执行。。。'
---- 使用事物 将数据移动到历史表
BEGIN TRANSACTION
BEGIN TRY
IF(@maxpage > 0)
BEGIN
--最后两天的计算方式
SET @mindate = DATEADD(DAY,(@maxday),@mindate)
END
ELSE
BEGIN
--最后一天的计算方式,最后一天
SET @mindate = DATEADD(DAY,(DATEDIFF(DAY,@mindate,@maxdate))-(@minday-1),@mindate)
END
PRINT '开始将旧表的数据插入到新表中:'
INSERT INTO KYT_History.dbo.Daily_StockSettlement_his SELECT * FROM Daily_StockSettlement WHERE DSS_CreateDate < @mindate ORDER BY DSS_CreateDate ASC
PRINT '开始删除旧表中的数据:'
DELETE FROM Daily_StockSettlement WHERE DSS_CreateDate < @mindate
PRINT '正在执行 '+ convert(varchar(10), @maxpage)+' 次循环,没有发生错误,正在执行 Commit 命令。'
--提交事物
COMMIT TRANSACTION
--最后将maxpage-1
SET @maxpage = @maxpage-1
--等待20秒后继续运行此循环
WAITFOR DELAY '00:00:10';
END TRY
BEGIN CATCH
PRINT '正在执行 '+ convert(varchar(10), @maxpage)+' 次循环,并且发生错误:执行 Rollback 命令。'
PRINT '错误内容:'+ERROR_MESSAGE()
--如果发生异常 抛出错误并回滚
ROLLBACK TRANSACTION
--如果发生异常马上结束循环
BREAK
END CATCH
END --while end
GO
/*
select ERROR_LINE() as Line,
ERROR_MESSAGE() as message1,
ERROR_NUMBER() as number,
ERROR_PROCEDURE() as proc1,
ERROR_SEVERITY() as severity,
ERROR_STATE() as state1
)*/