SQL Server 大量匯入資料實戰指南：四大主流方法效能對比與選型策略

業務場景與痛點

專案上線、系統遷移、異構同步、歷史資料歸檔——大量資料匯入是 DBA 與實施顧問的高頻任務。選錯方法會導致匯入耗時數倍、記錄檔暴增、鎖死線上業務。

方法一：T-SQL 原生跨資料庫查詢（同構、簡單）

SELECT INTO（建立新表並匯入）

SELECT * INTO TargetDB.dbo.NewTable
FROM SourceDB.dbo.SourceTable;

• ✅ 語法簡單、自動建立表結構（不含索引/約束/觸發器）。
• ❌ 目標表必須不存在；不支援合併（Upsert）；跨版本相容但僅限 SQL Server 間。

INSERT INTO ... SELECT（追加至既有表）

INSERT INTO TargetDB.dbo.TargetTable (col1, col2, ...)
SELECT col1, col2, ... FROM SourceDB.dbo.SourceTable;

• ✅ 支援合併、可指定欄位對應、相容型別隱性轉換（int→float 等）。
• ❌ 大量資料單一交易記錄檔膨脹、鎖升級風險。

優化技巧

• 分批次：WHERE id > @last_id ORDER BY id OFFSET 0 ROWS FETCH NEXT 50000 ROWS ONLY 迴圈。
• 最小化記錄：WITH (TABLOCK) + 簡單/大量記錄復原模式 → 最小記錄。
• 停用非叢集索引 → 匯入完成後重建。

方法二：Excel/CSV 中繼檔案（異構、需人工清洗）

適用場景

• 來源為 Oracle、MySQL、PostgreSQL、DB2、文字檔、ERP 匯出。
• 資料品質不佳，需人工/半自動清洗（去重、格式統一、業務規則驗證）。

工具鏈

1. 來源匯出 → CSV (UTF-8 BOM) / Excel (.xlsx)。
2. Excel Power Query / Python pandas / C# EPPlus 清洗轉型。
3. SSMS「匯入資料精靈」或 OPENROWSET 直接讀取：

SELECT * INTO TargetTable
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;Database=D:\Data\Source.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]');

優缺點

方法三：BCP / BULK INSERT / OPENROWSET(BULK) — 高效能原生載入

BCP 命令列（匯出/匯入）

-- 匯出
bcp "SELECT * FROM SourceDB.dbo.SourceTable" queryout D:\Data\Source.dat -S . -T -c -t, -r\n

-- 匯入（需目標表存在）
bcp TargetDB.dbo.TargetTable in D:\Data\Source.dat -S . -T -c -t, -r\n -b 50000 -h "TABLOCK, CHECK_CONSTRAINTS"

• -b 批次大小（建議 50,000~100,000）；-h TABLOCK 最小記錄；-c 字元模式通用。

BULK INSERT（T-SQL 內建）

BULK INSERT TargetDB.dbo.TargetTable
FROM 'D:\Data\Source.dat'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    BATCHSIZE = 50000,
    TABLOCK,
    CHECK_CONSTRAINTS,
    FIRSTROW = 2,  -- 跳過標題列
    CODEPAGE = '65001'  -- UTF-8
);

OPENROWSET(BULK) — 彈性最高

INSERT INTO TargetTable (col1, col2, ...)
SELECT col1, col2, ...
FROM OPENROWSET(BULK 'D:\Data\Source.dat',
    FORMATFILE = 'D:\Data\Source.fmt',
    FIRSTROW = 2) AS src;

格式檔 (`.fmt`) 可定義欄位對應、型別轉換、跳過欄位，支援複雜 ETL。

方法四：SSIS (SQL Server Integration Services) — 企業級 ETL 平台

• 視覺化設計、資料流/控制流分離、豐富轉換元件（查閱、聚合、腳本、CDC）。
• 支援平行處理、檢查點重啟、記錄/事件處理、參數化部署 (Project Deployment Model)。
• 適用：夜間批次 ETL、異構同步、複雜業務轉換、資料品質服務 (DQS) 整合。

效能對比與選型決策樹

最佳實務檢查清單

• [ ] 目標表非叢集索引**匯入前停用/刪除**，匯入後重建（ONLINE = ON）。
• [ ] 叢集索引鍵**與匯入排序一致**（避免頁分裂）。
• [ ] 使用 **簡單/大量記錄復原模式** + TABLOCK 達到最小記錄。
• [ ] 設定 BATCHSIZE 避免單一交易過大（建議 50k~100k）。
• [ ] 匯入後 **UPDATE STATISTICS WITH FULLSCAN** 或重建索引更新統計。
• [ ] 驗證資料筆數、檢查約束、抽樣比對業務關鍵欄位。