SQL Server 資料移動利器全解析：選對工具，事半功倍

面對異構資料來源、大量資料遷移、ETL 淨化轉換，SQL Server 提供三大主流方案：T-SQL 語句（OPENROWSET/OPENDATASOURCE）、BCP 命令列工具、DTS/SSIS（資料轉換服務/Integration Services）。本文從使用方式、靈活度、效能、程式化呼叫、適用場景五大維度深度對比，助你精準選型。

一、T-SQL 語句：OPENROWSET 與 OPENDATASOURCE

核心差異

特性	OPENDATASOURCE	OPENROWSET
用途	建立臨時連結伺服器參考	直接回傳資料列集（可當表用）
語法	OPENDATASOURCE(provider, conn_str)	OPENROWSET(provider, conn_str, query)
彈性	僅能引用遠端表/檢視，過濾需在本端 WHERE	可在遠端直接執行查詢/過濾/聚合
適用場景	臨時查詢遠端單一表	複雜異構查詢、將遠端結果直接 JOIN 本地表

範例：存取 Access (Jet OLEDB)

-- OPENDATASOURCE
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
    'Data Source=C:\data\abc.mdb;User ID=admin;Password=;')...table1;

-- OPENROWSET（更彈性）
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'C:\data\abc.mdb'; 'admin'; '', 'SELECT * FROM table1 WHERE id > 100');

優缺點總結

• 優點：無需建立連結伺服器、Ad-hoc 查詢極方便、T-SQL 熟手零學習成本。
• 缺點：需目標端安裝對應 OLE DB Provider、權限設定繁瑣、大量資料效能不如 BCP/SSIS、難以進行複雜轉換邏輯。

二、BCP (Bulk Copy Program)：高效能大量載入/匯出

核心特性

• 基於 DB-Library / ODBC / OLE DB，支援平行匯入（多執行緒同時寫入同一表），大幅提升載入速度。
• 支援格式檔 (.fmt) 精確對應欄位型別、終止符、固定寬度。
• 可匯出至 CSV、XML、原生格式；匯入支援 BULK INSERT / OPENROWSET(BULK...)。

常用指令範例

# 匯出 authors 表至 Excel（實為 tab-delimited text）
bcp pubs.dbo.authors out c:\temp\authors.xls -c -t, -T -S localhost

# 匯入（需目標表存在、權限足夠）
bcp pubs.dbo.authors in c:\temp\authors.xls -c -t, -T -S localhost -b 10000 -h "TABLOCK"

# 透過 xp_cmdshell 在 T-SQL 中呼叫
EXEC master..xp_cmdshell 'bcp pubs.dbo.authors out c:\temp\authors.xls -c -t, -T -S localhost';

參數速查：-c 字元模式、-t, 欄位分隔符、-T 信任連線、-b 批次大小、-h TABLOCK 鎖表加速。

優缺點總結

• 優點：速度最快（極簡協議、平行化）、無需圖形介面、適合排程自動化、Express 版也可用。
• 缺點：無內建轉換邏輯（需預先處理或配合格式檔）、錯誤處理弱（全批次失敗）、難以處理複雜關聯/查閱轉換。

三、DTS (SQL 2000) / SSIS (SQL 2005+)：企業級 ETL 平台

核心能力

• 圖形化設計師：拖拉元件（來源、轉換、目標），所見即所得。
• 豐富轉換元件：查閱、合併、條件分流、資料清洗、腳本元件 (C#/VB.NET)、資料分析、緩慢變維度 (SCD)。
• 封裝部署：檔案系統 / SSISDB 目錄 / 專案部署模型，支援參數化、環境變數、記錄、事件處理。
• 程式化控制：Microsoft.SqlServer.Dts.Runtime 組件載入/執行/監控封裝。

優缺點總結

• 優點：最強轉換能力、視覺化維護、企業級部署監控、支援異構來源/目標（Oracle、Flat File、Excel、ODBC、Azure、Hadoop 等）。
• 缺點：學習曲線陡峭、封裝開發/除錯耗時、執行效能較 BCP 略低（轉換開銷）、Express 版功能受限。

四、綜合選型決策矩陣

場景	首選工具	備選	關鍵理由
SQL Server 之間單表/全庫遷移，無複雜轉換	T-SQL (SELECT INTO / INSERT SELECT)	BCP	語法原生、無外部相依、效能佳
異構來源 定期全量/增量同步，需簡單清洗	SSIS	T-SQL + Agent Job	視覺化維護、內建查閱/轉換、錯誤處理完善
超大量資料 (TB 級) 初始載入/重建索引	BCP / BULK INSERT	SSIS (Fast Parse、平行)	極致吞吐、最小日誌、平行化
應用程式內嵌匯入匯出功能	T-SQL (OPENROWSET) 或 SMO	SSIS 程式化呼叫	輕量、無額外安裝
複雜 ETL：多來源合併、SCD、資料品質規則	SSIS	無	唯一能勝任的原生平台

效能優化金鑰（通用）

1. 最小日誌：目標表無索引、使用 TABLOCK、資料庫 SIMPLE 或 BULK_LOGGED 恢復模式。
2. 批次大小：-b 10000 (BCP) / ROWS_PER_BATCH (BULK INSERT)，平衡記憶體與鎖持有時間。
3. 平行度：多檔案並行 BCP、SSIS 多執行緒資料流、資料表分區平行載入。
4. 預先排序：依叢集索引鍵排序資料檔，避免載入時排序溢寫 tempdb。