如何将多表每日更新/新增行统计结果存入tablestats表？

不用游标，只需调整动态SQL的写法，将多表的统计结果通过UNION ALL合并为单一结果集，再通过临时表中转后用MERGE写入目标表，效率比游标高很多。

步骤1：确认目标表结构 #

假设stats.dbo.tablestats的结构如下（如果没有则先创建）：

CREATE TABLE stats.dbo.tablestats (
    table_name NVARCHAR(128),
    stats_date DATE,
    insert_count INT,
    update_count INT,
    PRIMARY KEY (table_name, stats_date) -- 确保日期+表名的唯一约束
);

步骤2：编写整合式动态SQL脚本 #

核心思路是拼接所有目标表的统计查询，用UNION ALL合并成一个结果集，存入临时表后再合并到目标表：

DECLARE @DynamicSQL NVARCHAR(MAX) = N'';

-- 拼接所有带last_upd列的表的统计逻辑（可通过WHERE过滤指定表）
SELECT @DynamicSQL += N'
SELECT 
    ''' + QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME) + ''' AS table_name,
    CAST(GETDATE() AS DATE) AS stats_date,
    -- 新增数：当天首次出现的行（通过主键判断历史是否存在）
    (SELECT COUNT(*) FROM ' + QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME) + ' curr
     WHERE curr.last_upd >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
       AND curr.last_upd < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)
       AND NOT EXISTS (
           SELECT 1 FROM ' + QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME) + ' prev
           WHERE prev.' + QUOTENAME(k.COLUMN_NAME) + ' = curr.' + QUOTENAME(k.COLUMN_NAME) + '
             AND prev.last_upd < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
       )) AS insert_count,
    -- 更新数：当天已存在的行发生更新
    (SELECT COUNT(*) FROM ' + QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME) + ' curr
     WHERE curr.last_upd >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
       AND curr.last_upd < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)
       AND EXISTS (
           SELECT 1 FROM ' + QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME) + ' prev
           WHERE prev.' + QUOTENAME(k.COLUMN_NAME) + ' = curr.' + QUOTENAME(k.COLUMN_NAME) + '
             AND prev.last_upd < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
       )) AS update_count
UNION ALL'
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.TABLES t 
    ON c.TABLE_SCHEMA = t.TABLE_SCHEMA 
    AND c.TABLE_NAME = t.TABLE_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k 
    ON t.TABLE_SCHEMA = k.TABLE_SCHEMA 
    AND t.TABLE_NAME = k.TABLE_NAME
    AND k.CONSTRAINT_NAME LIKE 'PK_%' -- 假设主键以PK_开头，可根据实际调整
WHERE c.COLUMN_NAME = 'last_upd'
  AND t.TABLE_TYPE = 'BASE TABLE'
  -- 可选：过滤需要统计的指定表
  -- AND QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME) IN ('[dbo].[Orders]', '[dbo].[Customers]');

-- 移除最后多余的UNION ALL
SET @DynamicSQL = LEFT(@DynamicSQL, LEN(@DynamicSQL) - 10);

-- 创建临时表存储合并后的统计结果
CREATE TABLE #TempStats (
    table_name NVARCHAR(128),
    stats_date DATE,
    insert_count INT,
    update_count INT
);

-- 执行动态SQL并写入临时表
INSERT INTO #TempStats
EXEC sp_executesql @DynamicSQL;

-- MERGE到目标表：存在则更新，不存在则插入
MERGE stats.dbo.tablestats AS target
USING #TempStats AS source
ON target.table_name = source.table_name 
   AND target.stats_date = source.stats_date
WHEN MATCHED THEN
    UPDATE SET 
        target.insert_count = source.insert_count,
        target.update_count = source.update_count
WHEN NOT MATCHED THEN
    INSERT (table_name, stats_date, insert_count, update_count)
    VALUES (source.table_name, source.stats_date, source.insert_count, source.update_count);

-- 清理临时表
DROP TABLE #TempStats;

关键注意事项 #

1. 主键依赖：脚本通过主键判断行是否存在，需确保所有统计的表都有主键；如果主键命名规则不是PK_开头，需要调整KEY_COLUMN_USAGE的过滤条件。
2. 统计逻辑调整：如果你的新增/更新判断逻辑不同（比如不需要对比历史，仅统计当天last_upd的总行数并拆分），可以修改子查询的条件，例如直接统计当天last_upd的行数作为总变动数，再根据业务规则拆分。
3. 可维护性优化：可以创建一个配置表（如stats.dbo.monitored_tables）存储需要统计的表名和主键列，动态SQL从该表读取，避免每次修改脚本。
4. SQL Agent配置：将上述脚本保存为SQL Server Agent作业，设置每日执行计划即可。

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