SQL Server 备份上周历史数据到历史表
<pre><code class="language-sql">
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: hunter
-- Create date: 2022年1月19日 14点58分
-- Description: 备份上周历史数据到历史表
-- =============================================
alter PROCEDURE proc_BackupsLastWeekInsertIoT_LineA
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON; --开启产生运行时错误,整个事务将终止并回滚
declare @tableName varchar(20);
declare @dateDay varchar(8);
declare @hisTableName varchar(50);
declare @sqlSelectText nvarchar(500);
declare @sqlDeleteText nvarchar(500);
declare @sqlAddKeyText nvarchar(500);
set @tableName='IoT_LineA1';
-- 上周周日的时间
set @dateDay=CONVERT(varchar(8) ,dateadd(WEEK, datediff(WEEK, 0, getdate())-1, 6), 112);
set @hisTableName=@tableName+ 'His' + @dateDay;
BEGIN TRY
BEGIN TRANSACTION;
-- 将上周数据写入临时表
set @sqlSelectText='select ID, DataKey, DataValue, CreatorTime into '+ @hisTableName +' from '+ @tableName +' with(NoLock) where CreatorTime between dateadd(WEEK, datediff(WEEK, 0, getdate())-1, 0) and dateadd(WEEK, datediff(WEEK, 0, getdate()), 0)';
Exec(@sqlSelectText);
-- 删除数据
set @sqlDeleteText='delete from '+ @tableName +' where EXISTS ( select 1 from '+ @hisTableName +' with(NoLock) where '+ @tableName +'.ID='+ @hisTableName +'.ID )';
Exec(@sqlDeleteText);
-- 给历史表增加主键,索引
set @sqlAddKeyText='ALTER TABLE '+ @hisTableName +' ADD CONSTRAINT [PK_'+ @hisTableName +'_ID] PRIMARY KEY([ID]);
CREATE NONCLUSTERED INDEX ID_His'+ @dateDay +' ON '+ @hisTableName +'(ID,CreatorTime) INCLUDE(DataKey,DataValue)';
Exec(@sqlAddKeyText);
print @sqlSelectText;
print @sqlDeleteText;
print @sqlAddKeyText;
SELECT 0 AS ErrorCode,'成功' AS ErrorMsg;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
--如果发生异常,且存在事务,则回滚。
ROLLBACK TRANSACTION;
SELECT -1 AS ErrorCode,'失败' AS ErrorMsg;
END CATCH
END
GO
</code></pre>