远荣软件

yuanrongruanjian


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>

页面列表

ITEM_HTML