SQL Server 動態 SQL 批次更新 1000 張同結構表：游標 + sp_executesql 實戰模式

問題情境

• 1000 張結構相同表 (欄位: qq,tj,yj,ej,sj,sij,wj,lj,zs,zjl)
• 更新來源表 jj (1000 列)，每列對應一張目標表
• 映射關係表 table_index (序號, 資料表名)
• 來源資料頻繁變動，需自動化、可重複執行

核心錯誤與修正

原始碼錯誤：嘗試直接使用變數 @table_name 作為表名 → T-SQL 不支援變數作為物件識別碼。

解法：動態 SQL + sp_executesql / EXEC()。

修正版完整存儲程序

USE fff;
GO

CREATE PROCEDURE usp_BatchUpdateTables
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @table_name SYSNAME,
            @qq INT, @tj INT, @yj INT, @ej INT, @sj INT,
            @sij BIGINT, @wj BIGINT, @lj BIGINT, @zs BIGINT, @zjl CHAR(15);

    -- 關聯查詢一次取得所有所需欄位，避免雙層游標
    DECLARE cur CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
        SELECT b.資料表名, a.qq, a.tj, a.yj, a.ej, a.sj, a.sij, a.wj, a.lj, a.zs, a.zjl
        FROM jj a
        INNER JOIN table_index b ON a.table_index = b.序號;

    OPEN cur;
    FETCH NEXT FROM cur INTO @table_name, @qq, @tj, @yj, @ej, @sj, @sij, @wj, @lj, @zs, @zjl;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 安全防禦：驗證表名合法性，防止 SQL 注入
        IF @table_name NOT LIKE '%[^a-zA-Z0-9_@#$]%' 
           AND EXISTS (SELECT 1 FROM sys.objects WHERE name = @table_name AND type = 'U')
        BEGIN
            DECLARE @sql NVARCHAR(MAX);
            SET @sql = N'INSERT INTO ' + QUOTENAME(@table_name) + 
                       N' (qq, tj, yj, ej, sj, sij, wj, lj, zs, zjl) ' +
                       N'VALUES (@qq, @tj, @yj, @ej, @sj, @sij, @wj, @lj, @zs, @zjl)';

            -- 參數化執行，效能優於 EXEC(字串)，防注入
            EXEC sp_executesql @sql,
                N'@qq INT, @tj INT, @yj INT, @ej INT, @sj INT, @sij BIGINT, @wj BIGINT, @lj BIGINT, @zs BIGINT, @zjl CHAR(15)',
                @qq, @tj, @yj, @ej, @sj, @sij, @wj, @lj, @zs, @zjl;
        END
        ELSE
        BEGIN
            PRINT '警告：跳過無效表名 ' + @table_name;
        END

        FETCH NEXT FROM cur INTO @table_name, @qq, @tj, @yj, @ej, @sj, @sij, @wj, @lj, @zs, @zjl;
    END

    CLOSE cur;
    DEALLOCATE cur;
END;
GO

關鍵優化點解析

進階：批次化提交控制交易日誌

-- 每 100 筆認可一次，避免長交易鎖定/日誌暴漲
DECLARE @batchCounter INT = 0;
WHILE @@FETCH_STATUS = 0
BEGIN
    -- ... 執行 INSERT ...
    
    SET @batchCounter += 1;
    IF @batchCounter % 100 = 0
    BEGIN
        COMMIT TRANSACTION;
        BEGIN TRANSACTION;  -- 隱含交易模式下需顯式控制
    END
    
    FETCH NEXT FROM cur INTO ...;
END
COMMIT TRANSACTION;  -- 尾數認可

替代方案比較

「偷懶」的最高境界是寫出可維護、可擴展、可觀測的自動化腳本。動態 SQL + 參數化 + 驗證 + 批次認可，是 T-SQL 自動化的專業標準配置。