三大方式總覽
| 維度 | T-SQL (OPENROWSET/OPENDATASOURCE) | BCP (Bulk Copy Program) | DTS/SSIS (Data Transformation Services) |
|---|
| 本質 | 分布式查詢 / 即席導入導出 | 命令行高性能批量複製工具 | ETL 平台、可視化設計、腳本擴展 |
| 異構數據源 | ✅ OLE DB Provider 通用 | ✅ OLE DB / ODBC 通用 | ✅ 豐富連接管理器 |
| 數據轉換能力 | ⚠️ 僅 SQL 表達式 | ❌ 無 (需預處理/後處理) | ✅ VBScript/JScript/C# 任意邏輯 |
| 性能 (大數據量) | 中等 (網絡+解析開銷) | **最高** (流式、並行、最小日志) | 高 (並行、緩衝、檢查點重啟) |
| 自動化/調度 | SQL Agent Job / 應用調用 | 批處理 / Agent Job / 應用調用 | SSIS 目錄部署、Agent Job、事件驅動 |
| 學習曲線 | 低 (SQL 應用開發者熟悉) | 低 (命令參數) | 中高 (包設計、變量、事件、腳本) |
| 版本現狀 | 持續增強 | 持續維護 | **DTS 已棄用 (2000) → SSIS (2005+)** |
T-SQL 異構導入導出實戰
OPENROWSET (推薦：單語句、行集源)
-- 從 Access 導入 SQL Server
SELECT * INTO SQL_TABLE
FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'C:\Data\source.mdb'; 'admin'; '',
'SELECT * FROM ACCESS_TABLE'
);
-- 導出到 Excel (需 ACE Provider)
INSERT INTO OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\Export\out.xlsx;HDR=YES',
'SELECT * FROM [Sheet1$]'
)
SELECT col1, col2 FROM SQL_TABLE;
OPENDATASOURCE (適用：引用遠程服務器對象)
SELECT *
FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=RemoteSrv;User ID=sa;Password=pass')
.RemoteDB.dbo.RemoteTable;
BCP 高性能批量操作
導出 (Native 格式最快)
bcp "SELECT * FROM db.dbo.table" queryout C:\export\table.dat -N -T -S localhost
bcp db.dbo.table out C:\export\table.bcp -N -T -S localhost
導入 (批量載入，最小日志)
bcp db.dbo.table in C:\export\table.bcp -N -T -S localhost -h "TABLOCK"
# -h "TABLOCK" 獲取表鎖，配合簡單恢復模式實現最小日志
關鍵參數
| 參數 | 含義 |
|---|
| -N | Native 格式 (二進制、保真、最快、跨 SQL Server 版本兼容) |
| -c | 字符格式 (文本、可讀、跨異構) |
| -w | Unicode 字符格式 |
| -b | 批次大小 (默認全量，建議 10000-50000) |
| -h "TABLOCK, CHECK_CONSTRAINTS" | 提示：表鎖、檢查約束 |
| -e err.log | 錯誤行記錄文件 |
SSIS (DTS 繼任者) 核心優勢
- **可視化設計器** (VS/BIDS/SSDT) 拖拽數據流任務。
- **數據流引擎**：緩衝區架構、並行執行、異步輸出。
- **腳本組件** (C#/VB.NET) 實現複雜清洗、查重、調用 Web API。
- **參數化/環境變量** 支持多環境部署 (Dev/Test/Prod)。
- **檢查點/重啟** 失敗處恢復，不重跑全量。
- **部署模型**：專案部署 (SSISDB 目錄) / 包部署 (文件系統/MSDB)。
選型決策樹
- **SQL Server 之間、一次性/低頻、數據量 < 100萬、無複雜轉換** → **T-SQL `SELECT INTO` / `INSERT INTO`**。
- **異構數據源、一次性/低頻、開發人員熟悉 SQL** → **T-SQL `OPENROWSET`**。
- **大數據量 (>100萬)、定期批量、追求極致性能、無複雜轉換** → **BCP (Native 格式 + TABLOCK)**。
- **定期 ETL、複雜清洗/轉換/查重/緩慢變維、需監控/重啟/版本控制** → **SSIS**。
- **應用集成、參數化導入、非 DBA 操作** → **應用層調用 BCP / SSIS 包 / T-SQL**。
性能實測參考 (1000 萬行、10 列、~2GB)
| 方式 | 導出耗時 | 導入耗時 | 備註 |
|---|
| T-SQL OPENROWSET | ~18 min | ~22 min | 單線程、解析開銷大 |
| BCP Native + TABLOCK | ~4 min | ~6 min | 並行寫入、最小日志 |
| SSIS (8 並行、快速解析) | ~5 min | ~7 min | 視覺化、可擴展、企業級 |
