SQL批量循环删除数据：统计总删除条数并邮件发送的实现

核心思路 #

提前初始化统计用的临时表（避免循环中重复创建报错），对每张表执行批量删除逻辑并累计删除条数，最后将统计结果生成邮件发送。

1. 初始化统计临时表 #

在所有清理逻辑前创建本地临时表，循环中仅插入数据而非重复建表：

-- 创建统计结果存储表（仅执行一次）
CREATE TABLE #DeleteStats (
    TableName NVARCHAR(128),
    DeleteCount INT
)

2. 单表批量删除+统计实现 #

以清理指定时间前的数据为例，每次批量删除500条，累计统计总删除数：

DECLARE @TableName NVARCHAR(128) = 'TableA'
DECLARE @BatchSize INT = 500
DECLARE @TotalDeleted INT = 0
DECLARE @DeletedCount INT = 0

WHILE 1=1
BEGIN
    -- 加ROWLOCK提示缩小锁范围，降低对业务的影响
    DELETE TOP(@BatchSize) WITH (ROWLOCK)
    FROM TableA
    WHERE CreatedTime < '2023-01-01' -- 替换为你的清理条件
    
    SET @DeletedCount = @@ROWCOUNT
    SET @TotalDeleted += @DeletedCount

    -- 没有数据可删时退出循环
    IF @DeletedCount = 0
        BREAK
END

-- 将当前表的统计结果插入临时表
INSERT INTO #DeleteStats (TableName, DeleteCount)
VALUES (@TableName, @TotalDeleted)

3. 多表批量处理方案 #

如果需要清理多张表，可通过游标遍历预定义的表列表，批量执行清理逻辑：

-- 定义需清理的表及对应过滤条件
CREATE TABLE #TablesToClean (
    TableName NVARCHAR(128),
    FilterCondition NVARCHAR(MAX)
)

INSERT INTO #TablesToClean
VALUES 
('TableA', 'CreatedTime < ''2023-01-01'''),
('TableB', 'ExpireTime < GETDATE()'),
('TableC', 'Id < 100000')

-- 游标遍历处理每张表
DECLARE @CurrentTable NVARCHAR(128), @Filter NVARCHAR(MAX)
DECLARE TableCursor CURSOR FOR
SELECT TableName, FilterCondition FROM #TablesToClean

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @CurrentTable, @Filter

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @TotalDeleted INT = 0
    DECLARE @DeletedCount INT = 0
    DECLARE @Sql NVARCHAR(MAX)

    WHILE 1=1
    BEGIN
        -- 动态SQL执行批量删除，用QUOTENAME避免注入风险
        SET @Sql = N'
        DELETE TOP(500) WITH (ROWLOCK)
        FROM ' + QUOTENAME(@CurrentTable) + '
        WHERE ' + @Filter + '
        SET @DeletedCount = @@ROWCOUNT'

        EXEC sp_executesql @Sql, N'@DeletedCount INT OUTPUT', @DeletedCount OUTPUT

        SET @TotalDeleted += @DeletedCount

        IF @DeletedCount = 0
            BREAK
    END

    -- 插入当前表的统计结果
    INSERT INTO #DeleteStats (TableName, DeleteCount)
    VALUES (@CurrentTable, @TotalDeleted)

    FETCH NEXT FROM TableCursor INTO @CurrentTable, @Filter
END

CLOSE TableCursor
DEALLOCATE TableCursor

4. 生成邮件并发送 #

将统计结果拼接为HTML格式邮件，通过SQL Server的邮件功能发送：

DECLARE @MailBody NVARCHAR(MAX)

-- 构建HTML邮件内容
SET @MailBody = N'
<h3>历史数据清理统计报告</h3>
<table border="1" cellpadding="4" cellspacing="0">
    <tr>
        <th>数据表名</th>
        <th>删除记录总数</th>
    </tr>'

SELECT @MailBody += N'
    <tr>
        <td>' + TableName + '</td>
        <td>' + CAST(DeleteCount AS NVARCHAR(20)) + '</td>
    </tr>'
FROM #DeleteStats

SET @MailBody += N'
</table>'

-- 发送邮件（替换为你的邮件配置和收件人）
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = '你的邮件配置文件名',
    @recipients = '收件人邮箱@xxx.com',
    @subject = '历史数据清理完成通知',
    @body = @MailBody,
    @body_format = 'HTML'

5. 清理临时表 #

执行完成后删除临时表释放资源：

DROP TABLE #DeleteStats
DROP TABLE #TablesToClean

关键注意事项 #

• 批量删除的TOP值（示例中为500）可根据数据库性能调整，避免长时间锁表。
• 动态SQL必须用QUOTENAME()处理表名，防止SQL注入。
• 生产环境建议在低峰期执行，或搭配事务控制（若需保证数据一致性）。

内容的提问来源于stack exchange，提问作者PrettyCode