sql server编写archive通用模板脚本实现自动分批删除数据

博主做过比较多项目的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_Part2
2. 组装的参数@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，后面取每批删除行数@@ROWCOUNT
print convert(varchar(50), getdate(), 121) + @strStepInfo
while @iRowCount <> 0
begin
  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 + 1
end'
-- ===== 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_Part2
print @sql
exec 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_Part2
print @sql
exec sp_executesql @sql, @parameters, N' 3 archive 表B', @ArchiveDate

总结

以上所述是小编给大家介绍的sql server编写archive通用模板脚本实现自动分批删除数据，希望对大家有所帮助，如果大家有任何疑问欢迎给我留言，小编会及时回复大家的！

赞 (0)

相关推荐

  • Sql Server 2000删除数据库备份文件

    复制代码 代码如下: /************************* Sql Server 2000 如何删除数据库备份 **************************/ --可以先备份数据库[TestDB] BACKUP DATABASE TestDB TO Disk ='E:\数据库备份\TestDB_20090829.bak' WITH INIT; go --添加备份数据库的逻辑变量名[testdbbk] sp_addumpdevice 'disk', 'testdbbk',

  • sql Server 2008 R2还原或删除数据库时总是出错的解决方法

    我们在还原或删除Windows Server 2008 R2数据库时经常出现因为数据库正在使用,所以无法获得对数据库的独占访问权等错误,非常烦恼,今天就教大家一招终极解决方法. 只需要新建一个查询,在里面执行下面代码即可: 复制代码 代码如下: ALTER DATABASE 你的数据库名称 SET OFFLINE WITH ROLLBACK IMMEDIATE ALTER DATABASE 你的数据库名称 SET OnLINE

  • Sql Server里删除数据表中重复记录的例子

    ［项目］ 数据库中users表,包含u_name,u_pwd两个字段,其中u_name存在重复项,现在要实现把重复的项删除! ［分析］ 1.生成一张临时表new_users,表结构与users表一样: 2.对users表按id做一个循环,每从users表中读出一个条记录,判断new_users中是否存在有相同的u_name,如果没有,则把它插入新表:如果已经有了相同的项,则忽略此条记录: 3.把users表改为其它的名称,把new_users表改名为users,实现我们的需要. ［程序］ 复制代

  • SQL Server2008 数据库误删除数据的恢复方法分享

    SQL Server2008 数据库误删除数据的恢复方法分享

    SQL Server中误删除数据的恢复本来不是件难事,从事务日志恢复即可.但是,这个恢复需要有两个前提条件: 1. 至少有一个误删除之前的数据库完全备份. 2. 数据库的恢复模式(Recovery mode)是"完全(Full)". 针对这两个前提条件,会有三种情况: 情况一.如果这两个前提条件都存在,通过SQL语句只需三步就能恢复(参考文章),无法借助第三方工具. a) 备份当前数据库的事务日志:BACKUP LOG [数据库名] TO disk= N'备份文件名' WITH NOR

  • SQLServer用t-sql命令批量删除数据库中指定表(游标循环删除)

    当我们需要批量删除数据库中的表时,对于单个删除一些表是否感到烦躁,厌倦,干脆写个脚本用得了. 本脚本使用游标循环删除,对于数量比较小,用游标暂不会造成恶劣影响. 复制代码 代码如下: DECLARE @tablename VARCHAR(30),@sql VARCHAR(500)DECLARE cur_delete_table CURSOR READ_ONLY FORWARD_ONLY FORSELECT name FROM sysobjects WHERE name LIKE 'PUB%' A

  • SQL Server数据库删除数据集中重复数据实例讲解

    SQL Server数据库操作中,有时对于表中的结果集,满足一定规则我们则认为是重复数据,而这些重复数据需要删除.如何删除呢?本文我们通过一个例子来加以说明. 例子如下: 如下只要companyName,invoiceNumber,customerNumber三者都相同,我们则认为是重复数据,下面的例子演示了如何删除. declare @InvoiceListMaster table ( ID int identity primary key , companyName Nchar(20), i

  • sql server编写archive通用模板脚本实现自动分批删除数据

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

  • sql server编写通用脚本实现获取一年前日期的方法

    问题: 在数据库编程开发中,有时需要获取一年前的日期,以便以此为时间的分界点,查询其前后对应的数据量.例如: 1. 想查询截止到一年前当天0点之前的数据量,以及一年前当天0点开始到现在的数据量. 2. 想查询截止到一年前当天24点之前的数据量,以及一年前当天24点开始到现在的数据量. 3. 想查询截止到一年前当月1日0点之前的数据量,以及一年前当月1日0点开始到现在的数据量. 4. 想查询截止到一年前当月最后一天24点之前的数据量,以及一年前当月最后一天24点开始到现在的数据量. 以上这四种情况

  • SQL SERVER编写存储过程小工具

    在开发数据库系统的过程中,经常要写很多的存储过程.为了统一格式和简化开发过程,我编写一些存储过程,用来自动生成存储过程.下面就为您简单介绍一下它们.其中一个用于生成Insert过程,另一个用于生成Update过程. Sp_GenInsert 该过程运行后,它为给定的表生成一个完整的Insert过程.如果原来的表有标识列,您得将生成的过程中的SET IDNTITY_INSERT ON 语句手工删除. 语法如下 sp_GenInsert < Table Name >,< Stored Pro

  • SQL Server一个字符串拆分多行显示或者多行数据合并成一个字符串

    SQL Server一个字符串拆分多行显示或者多行数据合并成一个字符串

    概述 STRING_AGG(合并):多行数据合并成一个字符串,以逗号隔开. STRING_SPLIT(拆分):一个字符串,拆分成多行. 一.多行数据合并成一个字符串 1.通过 FOR xml path('') 合并字符串记录 根据name字段,合并code declare @table1 table ( id int ,code varchar(10) , name varchar(20) ); insert into @table1 ( id,code, name ) values ( 1,

  • SQL SERVER数据库的作业的脚本及存储过程

    IF EXISTS (SELECT name FROM sysobjects WHERE name = N'cg_DoBackupJob' AND type = 'P') DROP PROCEDURE cg_DoBackupJob GO CREATE PROCEDURE [cg_DoBackupJob] @DataBaseName varchar(100), @FileHead varchar(50), @isFullBackup bit, -- 0 差量备份 1 完整备份 @FolderPat

  • SQL Server远程定时备份数据库脚本分享

    经常会有定时备份SQL Server数据库的需要.定时备份到本机的话,还是挺容易的,计划任务就可以完成,但如果是整机挂了,那备份到本机是没意义的,那么就需要来考虑备份到局域网中,其它电脑里. 下面就分享一份在网上找了之后,自己再简单整理过的代码,配合 SQL Server 代理中的作业功能,已经稳定运行一个星期了,每小时就备份一次. 复制代码 代码如下: -- 创建网络映射(Y是盘符:IP地址后面要带共享文件夹的名称:password是密码,双引号引起:account是远程电脑的登录名) exe

  • 使用SQL Server 获取插入记录后的ID(自动编号)

    最近在开发项目的过程中遇到一个问题,就是在插入一条记录的后要立即获取所在数据库中ID,而该ID是自增的,怎么做?在sql server 2005中有几种方式可以实现. 要获取此ID,最简单的方法就是在查询之后select @@indentity --SQL语句创建数据库和表 复制代码 代码如下: create database dbdemo go use dbdemo go create table tbldemo (     id int primary key identity(1,1),

  • SQL Server 分页查询通用存储过程(只做分页查询用)

    自开始做项目以来,一直在用.这段存储过程的的原创者(SORRY,忘记名字了),写得这段SQL代码很不错,我在这个基础上,按照我的习惯以及思维方式,调整了代码,只做分页查询用. /*---------------------------------------------- *procedure name : P_PageResult * author :Fay * create date : 2014-07-18 */ CREATE PROCEDURE prcPageResult -- 获得某一

  • SQL server 2008 数据库优化常用脚本

    --查询某个数据库的连接数 select count(*) from Master.dbo.SysProcesses where dbid=db_id() --前10名其他等待类型 SELECT TOP 10 * from sys.dm_os_wait_stats ORDER BY wait_time_ms DESC SELECT *FROM sys.dm_os_wait_stats WHERE wait_type like 'PAGELATCH%' OR wait_type like 'LAZ

  • Windows系统彻底卸载SQL Server通用方法(推荐!)

    目录 前言 一.停止 SQL Server 服务 二.卸载 SQL Server 数据库 三.删除 SQL Server 相关文件 四.删除 SQL Server 相关注册表 五.重启电脑 总结 前言 无论什么时候,SQL Server 的安装和卸载都是一件让我们头疼的事情.因为不管是 SQL Server 还是 MySQL 的数据库,当我们在使用数据库时因为未知原因出现问题,想要卸载重装时,如果数据库卸载不干净,就会导致数据库重装失败.所以学会卸载 SQL Server 还是很重要的,本篇文章

随机推荐