SQL Server 2000 高效批次插入百萬筆測試資料技巧

場景與需求

開發測試階段常需快速產生大量模擬資料以驗證分頁、索引、鎖定等效能表現。純 T-SQL 迴圈插入雖簡單，但逐筆 INSERT 效能極差。本文介紹三種進階批次插入技巧，效能提升百倍以上。

一、基礎版：WHILE 迴圈（教學用）

DECLARE @i INT = 1;
WHILE @i <= 1000000
BEGIN
    INSERT INTO manager (username, userpwd) VALUES ('kgdiwss', '250');
    SET @i += 1;
END

缺點：每筆觸發交易、寫入交易記錄、更新索引，100 萬筆約需 30~60 分鐘。

二、進階版：集合式單一 INSERT（推薦）

1. 利用系統表交叉聯結產生數列

INSERT INTO manager (username, userpwd)
SELECT TOP 1000000 'user_' + CAST(ROW_NUMBER() OVER (ORDER BY a.id) AS VARCHAR), 'pwd123'
FROM sysobjects a CROSS JOIN sysobjects b CROSS JOIN sysobjects c;

原理：sysobjects 約 2,000 列，三重 CROSS JOIN 產生 80 億列，TOP 截取所需筆數，ROW_NUMBER() 產生唯一流水號。單一陳述式、單一交易、最小化記錄。

2. 效能對比（測試環境：P4 2.4G/1GB RAM/SQL 2000 SP4）

三、專家版：BCP 大量複製工具

1. 產生資料檔 data.txt

user_1	pwd123
user_2	pwd123
...
user_1000000	pwd123

可用簡單腳本（Python/PowerShell/VBS）快速產生。

2. 執行 BCP 指令

bcp testdb.dbo.manager in data.txt -c -t"\t" -Slocalhost -Usa -Ppassword -b10000 -h"TABLOCK"

參數說明：-b10000 批次大小 10,000 筆；-h"TABLOCK" 取得表級鎖定，最小化記錄；-c 字元模式。

四、實務注意事項

• 復原模式：大量匯入前將資料庫設為 SIMPLE 或 BULK_LOGGED，避免交易記錄暴增；
• 索引策略：匯入前 DROP INDEX，匯入後重建，比維護即時索引快 10 倍以上；
• 檢查約束：ALTER TABLE ... NOCHECK CONSTRAINT ALL 暫停檢查，匯入後 CHECK CONSTRAINT ALL 重新驗證；
• 平行度：企業版可加 TABLOCK 配合 MAXDOP 實現平行插入。

五、結語

「用 SQL 寫迴圈插入」是新手常見誤區。SQL Server 專為集合操作設計，善用 CROSS JOIN 產生數列、BCP 批次匯入、最小化記錄 三大技巧，百萬筆資料從「喝杯咖啡等」變成「眨眼間完成」。實務上建議：測試資料產生腳本化、參數化，納入自動化部署流程。