sql server中怎么实现自动分批删除数据

68次阅读
没有评论

共计 2622 个字符,预计需要花费 7 分钟才能阅读完成。

这篇文章给大家介绍 sql server 中怎么实现自动分批删除数据,内容非常详细,感兴趣的小伙伴们可以参考借鉴,希望对大家能有所帮助。

博主做过比较多项目的 archive 脚本编写,对于这种删除数据的脚本开发,肯定是一开始的话用最简单的一个 delete 语句,然后由于部分表数据量比较大啊,索引比较多啊,会发现删除数据很慢而且影响系统的正常使用。然后就对 delete 语句进行按均匀数据量分批 delete 的改写,这样的话,原来的删除一个表用一个语句,就可能变成几十行,如果 archive 的表有十几个甚至几十个,那我们的脚本篇幅就非常大了,增加了开发和维护的成本,不利于经验比较少的新入职同事去开发 archive 脚本,也容易把注意力分散到所谓分批逻辑中。

根据这种情况,本周博主(zhang502219048)刚好在工作过程中,总结并编写了一个自动分批删除数据的模板,模板固定不变,只需要把注意力集中放在 delete 语句中,并且可以在 delete 语句中控制每批删除的数据量,比较方便,通过变量组装模板 sql,避免每个表就单独写一个分批逻辑的重复代码,化简为繁,增加分批删除一个表指定数据的话只需要增加几行代码就可以(如下所示中的 demo1 和 demo2)。

demo1:不带参数,根据表 tmp_Del 删除表 A 对应 ID 的数据。

demo2:带参数,根据 Date 字段是否过期删除表 B 对应数据。

具体请参考下面的脚本和相关说明,如有不懂的地方欢迎评论或私信咨询博主。

-- ===== 1  分批 archive 模板  =======================================================--【请不要修改本模板内容】/*  说明:1.  组装的 archive 语句为:@sql = @sql_Part1 + @sql_Del + @sql_Part22.  组装的参数 @parameters 为:@parameters = @parameters_Base +  自定义参数 3.  传入参数:@strStepInfo  需要 print 的 step 信息 4. archive 逻辑专注于 @sql_Del,而非分散于分批。*/declare @parameters nvarchar(max) =  , @parameters_Base nvarchar(max) = N @strStepInfo nvarchar(100) , @sql nvarchar(max) =  , @sql_Part1 nvarchar(max) = N declare @iBatch int = 1, -- 批次  @iRowCount int = -1 -- 删除行数,初始为 -1,后面取每批删除行数 @@ROWCOUNTprint convert(varchar(50), getdate(), 121) + @strStepInfowhile @iRowCount   0begin print  begin batch:  print @iBatch print convert(varchar(50), getdate(), 121) begin try begin tran , @sql_Del nvarchar(max) =   --@sql_Del 脚本需要根据实际情况在后续脚本中自行编写, @sql_Part2 nvarchar(max) = N  select @iRowCount = @@rowcount commit tran end try begin catch rollback tran print  -- Error Message: + convert(varchar, error_line()) +   |   + error_message() end catch waitfor delay  0:00:01  -- 延时  print convert(varchar(50), getdate(), 121) print  end batch  select @iBatch = @iBatch + 1end -- ===== 2 demo1(delete 语句不含参数):archive  表 A  =======================================================select @parameters = @parameters_Base +   -- 如果有需要增加自定义参数,在这里加,例如 @parameters = @parameters_Base +  , @ArchiveDate datetime , @sql_Del =   delete top (50000) tc_Del from  表 A  tc_Del inner join tmp_Del cd on cd.ID = tc_Del.ID select @sql = @sql_Part1 + @sql_Del + @sql_Part2print @sqlexec sp_executesql @sql, @parameters, N  2 archive  表 A -- ===== 3 demo2(delete 语句含参数):archive  表 B  =======================================================select @parameters = @parameters_Base +  , @ArchiveDaate datetime  -- 如果有需要增加自定义参数,在这里加,例如 @parameters = @parameters_Base +  , @ArchiveDate datetime , @sql_Del =   delete top (50000) from  表 B  where Date   @ArchiveDate select @sql = @sql_Part1 + @sql_Del + @sql_Part2print @sqlexec sp_executesql @sql, @parameters, N  3 archive  表 B , @ArchiveDate

关于 sql server 中怎么实现自动分批删除数据就分享到这里了,希望以上内容可以对大家有一定的帮助,可以学到更多知识。如果觉得文章不错,可以把它分享出去让更多的人看到。

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-08-03发表,共计2622字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)