如何用T-SQL创建存储过程动态合并多DeviceLogs表数据？

你有按DeviceLogs_月份_年份格式命名的表（如DeviceLogs_1_2023、DeviceLogs_2_2023），每月会新增同结构的月度表，以下是两种实现合并数据的存储方案：

方案一：将数据合并到固定汇总表 #

适合需要持久化存储合并数据的场景，先创建汇总表，再通过存储过程自动同步所有月度表的数据。

1. 创建汇总表（需与月度表结构一致） #

CREATE TABLE DeviceLogs_All (
    -- 复制DeviceLogs_xx_xxxx表的所有字段，示例如下：
    LogID INT,
    DeviceID VARCHAR(50),
    LogTime DATETIME,
    LogContent NVARCHAR(MAX),
    -- 可选：新增字段记录数据来源表
    SourceTableName NVARCHAR(100)
)

2. 编写同步存储过程 #

该过程会自动识别所有符合命名规则的月度表，避免重复插入数据：

CREATE PROCEDURE MergeDeviceLogs
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @TableName NVARCHAR(100);
    DECLARE @SQL NVARCHAR(MAX);
    DECLARE @ExistsCheck NVARCHAR(MAX);

    -- 游标遍历所有符合命名规则的表
    DECLARE TableCursor CURSOR FOR
        SELECT name 
        FROM sys.tables 
        WHERE name LIKE 'DeviceLogs_[0-9]_[0-9][0-9][0-9][0-9]' -- 匹配单数字月份表
           OR name LIKE 'DeviceLogs_[0-9][0-9]_[0-9][0-9][0-9][0-9]'; -- 匹配双数字月份表（如10、11、12月）

    OPEN TableCursor;
    FETCH NEXT FROM TableCursor INTO @TableName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 检查当前表数据是否已同步到汇总表
        SET @ExistsCheck = N'IF NOT EXISTS (SELECT 1 FROM DeviceLogs_All WHERE SourceTableName = ''' + @TableName + ''')
        BEGIN
            ';

        -- 拼接插入SQL
        SET @SQL = @ExistsCheck + N'INSERT INTO DeviceLogs_All
            SELECT *, ''' + @TableName + ''' AS SourceTableName
            FROM ' + @TableName + N'
        END';

        -- 执行动态SQL
        EXEC sp_executesql @SQL;

        FETCH NEXT FROM TableCursor INTO @TableName;
    END;

    CLOSE TableCursor;
    DEALLOCATE TableCursor;
END;

使用方式 #

新增月度表后，执行以下命令完成同步：

EXEC MergeDeviceLogs;

方案二：动态返回合并结果（无需汇总表） #

适合不需要持久化存储，仅需临时查询所有月度数据的场景：

CREATE PROCEDURE GetAllDeviceLogs
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX) = N'';
    DECLARE @TableName NVARCHAR(100);

    -- 游标遍历符合规则的表，按年、月排序
    DECLARE TableCursor CURSOR FOR
        SELECT name 
        FROM sys.tables 
        WHERE name LIKE 'DeviceLogs_[0-9]_[0-9][0-9][0-9][0-9]'
           OR name LIKE 'DeviceLogs_[0-9][0-9]_[0-9][0-9][0-9][0-9]'
        ORDER BY RIGHT(name,4), SUBSTRING(name, CHARINDEX('_', name)+1, CHARINDEX('_', name, CHARINDEX('_', name)+1)-CHARINDEX('_', name)-1);

    OPEN TableCursor;
    FETCH NEXT FROM TableCursor INTO @TableName;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @SQL <> N''
            SET @SQL = @SQL + N' UNION ALL ';
        
        SET @SQL = @SQL + N'SELECT *, ''' + @TableName + ''' AS SourceTableName FROM ' + @TableName;

        FETCH NEXT FROM TableCursor INTO @TableName;
    END;

    CLOSE TableCursor;
    DEALLOCATE TableCursor;

    -- 执行动态SQL返回合并结果
    EXEC sp_executesql @SQL;
END;

使用方式 #

直接执行存储过程获取所有合并数据：

EXEC GetAllDeviceLogs;

关键注意事项 #

• 所有DeviceLogs_xx_xxxx表的字段结构必须完全一致，否则UNION ALL或插入操作会报错。
• 若使用方案一，可配合SQL Server代理作业，设置每月自动执行存储过程完成同步。

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