萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

SQL Server 大量資料匯入四大方案對比:SELECT INTO、Excel、DTS/SSIS、異構 OPENROWSET

資料遷移上線必讀:四種批量匯入策略優劣勢全分析

專案實施中資料匯入佔據 60%+ 工時。選對工具能節省數周人力。本文對比 SQL Server 內建四大匯入路徑的適用邊界、操作步驟、效能與陷阱。

方案一:SELECT INTO / INSERT INTO ... SELECT (同質 SQL Server 間)

適用

  • 來源與目標皆為 SQL Server (任何版本相容)。
  • 結構已對齊,資料品質高,無需清洗。
  • 一次性全量遷移或定期全量刷新。

操作

-- 建表 + 匯入 (SELECT INTO 自動建表,不複製索引/約束/觸發器)
SELECT * INTO TargetDB.dbo.Products FROM SourceDB.dbo.Products;

-- 僅匯入資料 (目標表需預先存在)
INSERT INTO TargetDB.dbo.Products (ProdID, ProdName, Price)
SELECT ProdID, ProdName, Price FROM SourceDB.dbo.Products;

關鍵限制

  • **不複製索引、主鍵、外鍵、觸發器、權限、擴充屬性** —— 需事後補建。
  • **不支援異構資料源** (Oracle、MySQL、PostgreSQL 等)。
  • 目標表**必須為空** (SELECT INTO) 或需自行處理主鍵衝突 (INSERT INTO)。
  • 跨伺服器需建立 連結伺服器 或使用 OPENROWSET。

方案二:Excel/CSV 中間檔案人工清洗 (資料品質差、需業務確認)

流程

  1. 來源系統匯出 Excel (.xlsx) 或 CSV (UTF-8/BOM)。
  2. 業務/數據分析師在 Excel/Power Query 中:
    去重、補全缺失值、統一編碼 (如性別 M/F→1/0)、刪除測試資料、格式標準化。
  3. SSMS → 右鍵資料庫 → 工作 → **匯入資料** → 選擇 Excel/平面檔案來源 → 對應欄位 → 執行。

優缺點

優點缺點
直觀、業務可參與、清洗靈活**效能差**:Excel 10 萬行以上極慢、易當機
無需開發介面程式**人工介入**:非自動化、難版本控制、難重現
支援任意來源 (Oracle→CSV→SQL)**型別推斷不準**:Excel 前 8 行決定型別,長文本易截斷

進階:SQL Server 匯入精靈進階選項

  • 「編輯對應」→ 勾選「啟用識別插入」保留主鍵值。
  • 「進階」 → 逐欄指定 DataType、OutputColumnWidth、TextQualified。
  • 儲存為 .dtsx (SSIS 封裝) 供下次複用/排程。

方案三:SSIS (SQL Server Integration Services) 企業級 ETL 平台

核心優勢

  • **圖形化開發** (Control Flow + Data Flow) + **腳本元件** (C#/VB.NET) 無限擴展。
  • **高效能緩衝區架構**:預設 10MB 緩衝區、行級轉換、並行執行引擎。
  • **豐富來源/目的地**:ADO.NET、OLE DB、ODBC、Flat File、Excel、Azure Blob、HDFS、Salesforce...
  • **部署模型**:專案部署 (SSISDB 目錄、環境變數、版本控管) vs 封裝部署 (檔案系統/SQL Server/MSDB)。
  • **前端整合**:Web/App 呼叫 catalog.create_execution 觸發封裝,回傳執行 ID 追蹤狀態。

適用場景

  • 每日/每小時增量同步 (CDC、時間戳、CDC)。
  • 複雜轉換:查找、合併、條件分流、錯誤流導向錯誤表。
  • 異構資料整合:Oracle → SQL Server、MySQL → SQL Server、API → SQL Server。
  • 需審計、重跑、參數化、事件處理的生產級 ETL。

開發複雜度提醒

SSIS 學習曲線陡峭,COM 介面程式碼冗長。小專案、一次性匯入、無複雜轉換 時,方案一/二 ROI 更高。

方案四:OPENROWSET / OPENDATASOURCE 即時異構查詢 (輕量、無 ETL 基建)

語法對比

-- OPENDATASOURCE: 僅開啟來源,適合整表/檢視表
SELECT * INTO LocalDB.dbo.Orders 
FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=192.168.1.10;User ID=u;Password=p').ERP.dbo.Orders;

-- OPENROWSET: 支援即時 WHERE 過濾,單次查詢更靈活
SELECT * INTO LocalDB.dbo.Orders_2024
FROM OPENROWSET('SQLOLEDB', 'Server=192.168.1.10;UID=u;PWD=p;Database=ERP',
    'SELECT OrderID, CustID, Amt FROM dbo.Orders WHERE OrderDate >= ''2024-01-01''');

Oracle 範例

SELECT * FROM OPENROWSET('OraOLEDB.Oracle', 
    'Data Source=ORCL_TNS;User ID=ora;Password=pwd', 
    'SELECT * FROM SCOTT.EMP WHERE DEPTNO = 10');

限制與風險

  • **無法進行複雜資料驗證/轉換** (需在目標端事後處理)。
  • **分散式查詢計畫不穩定**:遠端統計資訊缺失,優化器易誤估,大表 JOIN 災難。
  • **安全性**:需啟用 Ad Hoc Distributed Queries (預設關閉),開放 OLE DB Provider 攻擊面。
  • **不支援交易回滾** 跨伺服器 (除非啟用 MSDTC 且配置分散式交易)。

選型決策樹

  1. **同質 SQL Server、結構對齊、一次性全量** → 方案一 (SELECT INTO/INSERT SELECT)。
  2. **資料臟、需業務清洗、一次性/低頻** → 方案二。
  3. **生產級週期性 ETL、複雜轉換、異構、需監控/重跑/參數化** → 方案三 (SSIS)。
  4. **即時查詢遠端小表、無 ETL 基建、臨時分析** → 方案四。

效能基準參考 (百萬行、寬表、千兆網路)

方案耗時CPU (目標端)網路備註
INSERT SELECT (Linked Server)45 秒中高單執行緒
SSIS (Data Flow, 緩衝區 10MB, 並行 4)18 秒高高可調優緩衝區/並行度
BCP OUT + BCP IN (原生模式)22 秒低中命令列、無轉換最快
OPENROWSET50 秒中高受遠端計畫影響大
copyright © 萬盛學電腦網 all rights reserved