SQL Server 自動化合併日誌表儲存程序：動態游標遍歷、條件篩選與批次匯入

應用場景

系統依日期產生大量同結構日誌表 (如 log20230101, log20230102...)，需定期合併至單一歷史表供查詢分析。

儲存程序完整實作

CREATE PROCEDURE dbo.MergeLogTables
    @TargetTable   SYSNAME,   -- 目標歷史表 (如 LogHistory)
    @StartTable    SYSNAME,   -- 起始表名 (如 log20230101)
    @EndTable      SYSNAME    -- 結束表名 (如 log20230131)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX), @TableName SYSNAME, @Prefix VARCHAR(10);
    DECLARE @TargetExists BIT;

    -- 1. 檢查目標表是否存在，不存在則建立 (結構同來源表)
    SELECT @TargetExists = COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @TargetTable;
    IF @TargetExists = 0
    BEGIN
        -- 取得第一個來源表結構建立目標表
        SELECT TOP 1 @TableName = TABLE_NAME 
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_NAME LIKE 'log%' AND TABLE_NAME BETWEEN @StartTable AND @EndTable
        ORDER BY TABLE_NAME;
        
        SET @SQL = N'SELECT * INTO ' + QUOTENAME(@TargetTable) + N' FROM ' + QUOTENAME(@TableName) + N' WHERE 1=0';
        EXEC sp_executesql @SQL;
        PRINT '已建立目標表 ' + @TargetTable;
    END

    -- 2. 宣告游標遍歷符合條件的日誌表
    DECLARE cur CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
        SELECT TABLE_NAME 
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_NAME LIKE 'log%' 
          AND TABLE_NAME BETWEEN @StartTable AND @EndTable
        ORDER BY TABLE_NAME;

    OPEN cur;
    FETCH NEXT FROM cur INTO @TableName;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 3. 驗證結構相容 (簡易版：欄位數與名稱相符)
        IF EXISTS (
            SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_NAME = @TableName
            EXCEPT
            SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_NAME = @TargetTable
        )
        BEGIN
            PRINT '跳過結構不符表: ' + @TableName;
        END
        ELSE
        BEGIN
            -- 4. 批次匯入 (避免記錄檔暴增可分批)
            SET @SQL = N'INSERT INTO ' + QUOTENAME(@TargetTable) + N' SELECT * FROM ' + QUOTENAME(@TableName);
            EXEC sp_executesql @SQL;
            PRINT '已合併: ' + @TableName + ' (' + CAST(@@ROWCOUNT AS VARCHAR) + ' 筆)';
        END
        FETCH NEXT FROM cur INTO @TableName;
    END
    CLOSE cur; DEALLOCATE cur;
END
GO

執行範例

-- 合併 2023 年 1 月所有日誌表
EXEC dbo.MergeLogTables 'LogHistory', 'log20230101', 'log20230131';

進階增強建議

1. 分批 Commit： 大表時在游標迴圈中每 N 筆或每張表執行 CHECKPOINT 或切換簡單復原模式。
2. 分區表替代： SQL Server 2005+ 建議使用資料表分區，以日期為分區鍵，避免物理合併操作。
3. 記錄合併狀態： 建立 LogMergeHistory 表記錄每次執行時間、範圍、筆數、耗時，支援稽核與重跑。
4. 並行處理： 使用 Service Broker 或 SQL Agent 作業並行處理多個日期範圍。
5. 結構變更偵測： 比對 COLUMNPROPERTY、INFORMATION_SCHEMA.COLUMNS 完整定義 (型別、長度、精度、排序規則)。

效能優化提示

• 目標表建立 CLUSTERED INDEX 於日期欄位，符合查詢模式。
• 匯入前 DISABLE TRIGGER、DROP INDEX (非叢集)，匯入後重建。
• 使用 TABLOCK 提示加速大量插入：INSERT INTO Target WITH (TABLOCK) SELECT ...。