SQL Server 合併大量同結構日誌表的自動化預存程序

痛點場景

系統產生大量同結構日誌表（如 log20230101、log20230102...），單表查詢不便、跨表統計痛苦。需自動合併指定範圍表到單一目標表。

預存程序 BackupData

CREATE PROCEDURE BackupData
    @TableTarget sysname,   -- 合併後目標表
    @TableStart sysname,    -- 起始表名（含）
    @TableEnd sysname       -- 結束表名（含）
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @TableName sysname, @TablePref sysname, @IsTargetExist int;
    DECLARE tnames_cursor CURSOR LOCAL FAST_FORWARD FOR
        SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES;

    -- 1. 目標表不存在則建立（結構以實際需求調整）
    SELECT @IsTargetExist = COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TableTarget;
    IF @IsTargetExist = 0
    BEGIN
        DECLARE @ddl nvarchar(max) = N'CREATE TABLE ' + QUOTENAME(@TableTarget) + N'(
            [LOG1] nvarchar(10) NULL,
            [LOG2] nvarchar(10) NULL
            -- 其餘欄位依實際表結構補全，注意：目標表不得有與來源同名的 IDENTITY 欄位
        )';
        EXEC sp_executesql @ddl;
    END

    OPEN tnames_cursor;
    FETCH NEXT FROM tnames_cursor INTO @TableName;

    WHILE @@FETCH_STATUS <> -1
    BEGIN
        IF @@FETCH_STATUS <> -2
        BEGIN
            SET @TableName = RTRIM(@TableName);
            SET @TablePref = LEFT(@TableName, 3);  -- 依命名規則調整，如 'log'

            -- 2. 符合前綴且在範圍內
            IF @TablePref = 'log' AND @TableName >= @TableStart AND @TableName <= @TableEnd
            BEGIN
                DECLARE @ins nvarchar(max) = N'INSERT INTO ' + QUOTENAME(@TableTarget) + N' SELECT * FROM ' + QUOTENAME(@TableName);
                EXEC sp_executesql @ins;
                PRINT '表 ' + @TableName + ' 已導入 ' + @TableTarget;
            END
        END
        FETCH NEXT FROM tnames_cursor INTO @TableName;
    END

    CLOSE tnames_cursor;
    DEALLOCATE tnames_cursor;
END;
GO

使用範例

-- 合併 2023 年 8 月所有日誌表到 _logs202308
EXEC BackupData '_logs202308', 'log20230801', 'log20230831';

改良建議

• 欄位清單化：INSERT INTO target (col1, col2...) SELECT col1, col2... FROM source，避免結構變更導致位置錯誤。
• 分批提交：大表合併時每 N 筆 COMMIT 或使用 TOP (10000) 迴圈，避免長交易鎖定與記錄檔暴增。
• 分區表替代：SQL Server 2005+ 建議改用 資料表分區，以分區切換（SWITCH）秒級完成歸檔，無需資料搬移。
• 權限最小化：僅授予執行該程序的帳號 INSERT、SELECT、VIEW DEFINITION 權限。