【SQL Server密技】一次精通SELECT INTO批次建表，效能優化的絕佳解決方案！

為什麼要把大表拆成小表

一張上千萬筆的資料表，查詢愈跑愈慢、維護視窗愈拉愈長，這幾乎是每個後端工程師遲早會碰到的場景。把它依某種規則拆成多張較小的表，是其中一種權宜手段：查詢範圍縮小、維護單位變細、歸檔也比較好搬。

SQL Server 的 SELECT INTO 很適合做這件事——它能根據查詢結果直接生出一張新表，省去你先寫一段 CREATE TABLE 再 INSERT 的功夫。但它「省」掉的東西比你以為的多，這篇就把運作原理講清楚，順便把幾個照抄會直接報錯的地雷標出來。

SELECT INTO 到底複製了什麼

先把最容易誤會的觀念釘死。執行這一句：

SELECT * INTO 新資料表 FROM 來源資料表;

SQL Server 會做的事是：

• 建立一張新表
• 複製來源欄位的「名稱、資料型別、可空性」，以及 IDENTITY 屬性
• 把符合條件的資料列寫進去

它不會複製的，才是重點：索引、主鍵、外鍵、DEFAULT、CHECK 等任何約束，全部都不跟過來。微軟官方文件對 INTO 子句的描述很直接：「來源表上定義的索引、約束與觸發程序不會轉移到新表，你也無法在 SELECT...INTO 語句裡指定它們。」

這代表什麼？你拆出來的每一張小表都是「裸表」——只有欄位和資料，沒有任何索引或主鍵。如果你以為拆完就能直接享受原表的索引加速，那查詢只會更慢。要用，就得在拆完之後手動把需要的索引補回去。後面的效能段落會再提這點。

批次拆表的核心程式碼

思路是用 ROW_NUMBER() 給每一列編號，再用 WHILE 迴圈每次切一段範圍，拼出動態 SQL 建一張新表。下面是一份可以直接執行的版本，幾個常見地雷都已經填掉，逐行說明緊接在後：

-- 變數一律宣告在批次最上層，不要塞進迴圈裡
DECLARE @StartRow      INT,
        @EndRow        INT,
        @BatchSize     INT,
        @BatchNumber   INT,
        @TotalRows     INT,
        @TablePrefix   SYSNAME,
        @NewTable      SYSNAME,
        @SQL           NVARCHAR(MAX);

-- 初始化
SET @StartRow    = 1;
SET @BatchSize   = 500;            -- 每張新表存 500 筆
SET @BatchNumber = 1;
SET @TablePrefix = N'UserInfo_Batch_';

-- 總筆數只算一次，存進變數；不要每圈都掃全表
SELECT @TotalRows = COUNT(*) FROM dbo.UserInfo;

WHILE @StartRow <= @TotalRows
BEGIN
    SET @EndRow   = @StartRow + @BatchSize - 1;
    SET @NewTable = @TablePrefix + CAST(@BatchNumber AS NVARCHAR(10));

    -- 用 QUOTENAME 包住表名，避免動態 SQL 的識別字注入；
    -- 內層 SELECT 只挑真正要的欄位，不要把 RowNum 帶進新表
    SET @SQL = N'SELECT u.col1, u.col2, u.col3 ' +     -- 換成你實際要的欄位
               N'INTO ' + QUOTENAME(@NewTable) + N' ' +
               N'FROM ( ' +
               N'    SELECT col1, col2, col3, ' +
               N'           ROW_NUMBER() OVER (ORDER BY id) AS RowNum ' +
               N'    FROM dbo.UserInfo ' +
               N') AS u ' +
               N'WHERE u.RowNum BETWEEN @s AND @e;';

    EXEC sys.sp_executesql @SQL,
         N'@s INT, @e INT', @s = @StartRow, @e = @EndRow;

    SET @StartRow    = @EndRow + 1;
    SET @BatchNumber = @BatchNumber + 1;
END;

逐段拆解：每個陷阱在哪

一、WHILE ... BEGIN 一定要配一個 END

這是最容易漏掉、也最致命的一個。BEGIN 開了區塊，迴圈體寫完一定要用 END 收尾，否則 SQL Server 會在解析時就報 Incorrect syntax near ... expecting END，整段根本送不出去。網路上很多範例剪貼到一半就把 END 弄丟了，照抄必然卡在這裡。

二、變數宣告放在批次最上層，別塞進迴圈

T-SQL 的變數是在「編譯期」一次掃描整個批次時就全部建立好的，控制流程（包含 WHILE）在這個階段被忽略。所以就算你把 DECLARE @SQL 寫在迴圈體內，它也只會在編譯期跑一次、不會每圈重新宣告。

換句話說，單純把宣告放迴圈裡並不會讓它報「已宣告」的錯——那個錯只有在你對同一個變數 DECLARE 兩次才會出現。但把宣告塞在迴圈內純粹是壞習慣：讀的人會以為它每圈重來，反而誤解程式行為。把所有 DECLARE 集中在批次開頭，是可讀性與維護性的基本盤。

三、用 sp_executesql + QUOTENAME，不要裸 EXEC 字串拼接

原始寫法用 EXEC (@SQL) 執行一段純字串拼接的動態 SQL，表名直接黏進字串裡——這就是 SQL injection 的典型反模式。雖然這個例子的表名前綴是你自己寫死的、風險看似不高，但只要哪天前綴或範圍值來自外部輸入，整段就門戶大開。

正確做法分兩半：

• 能參數化的值（這裡的起訖列號）交給 sp_executesql 的參數，像上面的 @s、@e。
• 不能參數化的結構元素（表名這種識別字，sp_executesql 沒辦法當參數綁）用 QUOTENAME() 包起來。QUOTENAME 會把識別字用 [ ] 正確跳脫，遇到含特殊字元或不成對符號的惡意輸入會直接擋掉。

四、SELECT * 會把 RowNum 一起帶進新表

這是個很隱蔽的坑。如果內層子查詢用 SELECT *, ROW_NUMBER() ...，再用 SELECT * INTO 把整包灌進新表，那個臨時編號用的 RowNum 欄位會一起被寫進每一張批次表。結果就是：你的小表多了一根原表沒有的欄位，「結構跟來源一致」這個說法當場破功。

解法就是上面那樣，內外層都明確列出要的欄位，把 RowNum 留在 WHERE 過濾用、不要 SELECT 出來。

五、COUNT(*) 只算一次

原始寫法每一圈迴圈條件都重跑一次 SELECT COUNT(*) FROM UserInfo，等於對整張大表做 N 次全表掃描——一篇談效能的文章用這種寫法實在說不過去。把總筆數先 SELECT 進變數，迴圈只比對變數即可。

順帶一個更深的隱憂：整個分批過程中對 UserInfo 沒有任何鎖定或快照，如果中途有人寫入或刪除，ROW_NUMBER() 的編號會跟著變動，分頁就會錯位、漏抓或重複。要嚴謹一點，可以在交易裡用快照隔離等級，或先把資料抓進一張暫存表再分批，這部分依你的一致性需求斟酌。

執行後會長出什麼

跑完之後，資料庫裡會多出一串以 @TablePrefix 為前綴、編號遞增的表。用上面的設定（前綴 UserInfo_Batch_、每批 500 筆），會是：

• UserInfo_Batch_1（第 1–500 筆）
• UserInfo_Batch_2（第 501–1000 筆）
• UserInfo_Batch_3（第 1001–1500 筆）
• ……依此類推

表名前綴是哪個變數設的、輸出就該對得上，這點看似廢話，但前面提到很多範例會在這裡前後不一致（程式設 UserInfo_Batch_、結果卻寫 Data_1），讀者跟著對照就亂了。

加上錯誤處理

實務上一定要把執行包進 TRY...CATCH，至少知道是哪一批爆了：

BEGIN TRY
    EXEC sys.sp_executesql @SQL,
         N'@s INT, @e INT', @s = @StartRow, @e = @EndRow;
END TRY
BEGIN CATCH
    SELECT @BatchNumber          AS FailedBatch,
           ERROR_NUMBER()        AS ErrorNumber,
           ERROR_MESSAGE()       AS ErrorMessage;
    -- 視情況決定要中斷整批還是跳過這批繼續
    BREAK;
END CATCH;

效能與實務注意事項

前面說過 SELECT INTO 拆出來的是裸表，所以效能優化的第一步反而是「拆完之後」：

1. 拆完手動補索引。 新表沒有任何索引，常用的查詢欄位該建的索引要自己加回去，不然拆表反而拖慢查詢。
2. 批次大小依資源調。 500 只是起點，記憶體與交易日誌吃不吃得消，要看實際機器。
3. 挑離峰時段跑。 大量建表與寫入會搶資源，別在尖峰時段做。
4. 先盤點空間。 確認資料檔與日誌檔都有足夠成長空間，SELECT INTO 在完整復原模式下仍會產生可觀的日誌。
5. 防表名衝突。 執行前確認前綴加編號不會撞到既有的表，必要時先 IF OBJECT_ID(...) IS NOT NULL 檢查。
6. 確認權限。 執行帳號需要在目標結構描述上有 CREATE TABLE 權限，這是 SELECT INTO 的硬性要求。

什麼時候適合用這招

這套做法適合一次性的拆表、資料歸檔、或把歷史資料搬去獨立小表這類批次任務。但要說清楚：依編號硬切成 _1、_2、_3 的做法，後續查詢得自己判斷資料落在哪張表，維護成本不低。

如果你的需求是「長期、依日期或某個 key 自動分流」的水平切割，SQL Server 本身的資料表分割（Table Partitioning）才是正統解——同一張邏輯表底下分多個 partition，查詢仍寫一張表、引擎自動做分割區裁剪（partition elimination），不必自己拼一堆表名。SELECT INTO 批次建表是輕量、能立刻動手的工具；長期方案還是該評估分割表。

小結

SELECT INTO 搭批次處理確實能快速把大表切小，但這篇真正想留給你的是三件事：它只複製欄位不複製索引約束、動態 SQL 要用 sp_executesql 加 QUOTENAME 而不是裸 EXEC 拼字串、還有 WHILE 別忘了 END、COUNT(*) 別每圈重算。把這幾個坑避開，這段程式碼才是真的能上線跑的版本，而不是貼上去就報錯的範例。

參考資源

• INTO 子句 (Transact-SQL) — Microsoft Learn
• sp_executesql (Transact-SQL) — Microsoft Learn
• QUOTENAME (Transact-SQL) — Microsoft Learn
• 分割資料表與索引 — Microsoft Learn