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