介绍
这篇文章主要介绍了sql server编写档案通用模板脚本如何实现自动分批删除数据,具有一定借鉴价值,感兴趣的朋友可以参考下,希望大家阅读完这篇文章之后大有收获、下面让小编带着大家一起了解一下。
做过比较多项目的存档脚本编写,对于这种删除数据的脚本开发,肯定是一开始的话用最简单的一个删除语句,然后由于部分表数据量比较大啊,索引比较多啊,会发现删除数据很慢而且影响系统的正常使用,然后就对删除语句进行按均匀数据量分批删除的改写,这样的话,原来的删除一个表用一个语句,就可能变成几十行,如果存档的表有十几个甚至几十个,那我们的脚本篇幅就非常大了,增加了开发和维护的成本,不利于经验比较少的新入职同事去开发档案脚本,也容易把注意力分散到所谓分批逻辑中。
根据这种情况,编写了一个自动分批删除数据的模板,模板固定不变,只需要把注意力集中放在删除语句中,并且可以在删除语句中控制每批删除的数据量,比较方便,通过变量组装模板sql,避免每个表就单独写一个分批逻辑的重复代码,化简为繁,增加分批删除一个表指定数据的话只需要增加几行代码就可以(如下所示中的demo1和以及接下来)。
demo1:不带参数,根据表tmp_Del删除表一个对应ID的数据。
以及接下来:带参数,根据日期字段是否过期删除表B对应数据。
具体请参考下面的脚本和相关说明
-,=====, 1,分批档案模板,=======================================================厩氩灰薷谋灸0迥谌荨?*, 说明: 1只组装的存档语句为:@sql =, @sql_Part1 +, @sql_Del + @sql_Part2 2只组装的参数@ phase为:@parameters =, @parameters_Base +,自定义参数 3只传入参数:@strStepInfo 需要打印的步骤信息 4只存档逻辑专注于@sql_Del,而非分散于分批。 */declare @parameters  nvarchar (max),=, & # 39; & # 39; ,@parameters_Base nvarchar (max),=, n # 39; @strStepInfo nvarchar (100) & # 39; ,@sql nvarchar (max),=, & # 39; & # 39; ,@sql_Part1 nvarchar (max),=, n # 39; declare @iBatch int =, 1,,,,,批次 ,,,@iRowCount int =, 1,,删除行数,初始为1,后面取每批删除行数@@ROWCOUNT print 转换(varchar(50),获取当前日期(),,121),+ @strStepInfo ,while @iRowCount & lt的在0 开始 print 才能;& # 39;& # 39;begin 批:& # 39;& # 39; print @iBatch才能 print 才能;转换(varchar(50),获取当前日期(),,121) begin 才能试一试 ,,,begin tran & # 39; ,@sql_Del nvarchar (max),=, & # 39; & # 39;,,@sql_Del脚本需要根据实际情况在后续脚本中自行编写 ,@sql_Part2 nvarchar (max),=, n # 39;,, ,,,,,select @iRowCount =@@rowcount ,,,commit tran 最终获得才能试一试 begin 才能赶上 ,,,rollback tran ,,,print & # 39; & # 39;——, Error 信息:& # 39;& # 39;,+,转换(varchar, error_line ()), +, & # 39; & # 39;, |, & # 39; & # 39;, +, error_message () 最终获得才能赶上 waitfor 才能;delay & # 39; & # 39; 0:00:01& # 39; & # 39;,,延时 print 才能;转换(varchar(50),获取当前日期(),,121) print 才能;& # 39;& # 39;最终获得批# 39;& # 39; select 才能;@iBatch =, @iBatch + 1 结束# 39; -,=====,2,demo1(删除语句不含参数):archive 表A =======================================================select @parameters =, @parameters_Base +, & # 39; & # 39;,,如果有需要增加自定义参数,在这里加,例如@parameters =, @parameters_Base +, & # 39;, @ArchiveDate datetime # 39;=,@sql_Del & # 39; ,,,,,delete top (50000), tc_Del ,,,,,得到表A tc_Del ,,,,,inner join tmp_Del cd 提醒cd.ID =tc_Del.ID & # 39; select @sql =, @sql_Part1 +, @sql_Del + @sql_Part2 print @sql exec sp_executesql  @sql,, @ phase,, n,, 2, archive 表一个# 39; -,=====,3,以及接下来(删除语句含参数):archive 表B =======================================================select @parameters =, @parameters_Base +, & # 39;, @ArchiveDaate datetime # 39;,,如果有需要增加自定义参数,在这里加,例如@parameters =, @parameters_Base +, & # 39;, @ArchiveDate datetime # 39;=,@sql_Del & # 39; ,,,,,delete top (50000), ,,,,,得到表B ,,,,,where Date & lt; @ArchiveDate & # 39; select @sql =, @sql_Part1 +, @sql_Del + @sql_Part2 print @sql exec sp_executesql  @sql,, @ phase,, n,, 3, archive 表b # 39;,, @ArchiveDatesql server编写档案通用模板脚本如何实现自动分批删除数据