萬盛學電腦網

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

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

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

-- 建表+匯入 (目標表不存在)
SELECT * INTO TargetDB.dbo.NewTable FROM SourceDB.dbo.SourceTable;

-- 僅匯入資料 (目標表已存在)
INSERT INTO TargetDB.dbo.TargetTable SELECT * FROM SourceDB.dbo.SourceTable;

限制:不複製索引/約束/觸發器;目標表若有資料用 INSERT;僅限 SQL Server 間。

方案二:Excel/CSV 中轉(資料清洗首選)

  1. 來源匯出 Excel/CSV(Oracle 等可用 SQL Developer/expdp 導 CSV);
  2. Excel 清洗:排序刪除廢棄列、替換髒字元、格式統一(日期/數值/布林);
  3. SSMS 右鍵資料庫 → 工作 → 匯入資料 → 選 Excel/平面檔案來源 → 對應欄位 → 執行。

優點:直觀、清洗靈活、相容性最強;缺點:Excel 百萬列以上極慢/記憶體炸,大量資料改用 CSV + BCP/SSIS。

方案三:SSIS (SQL 2005+ 取代 DTS)

  • 圖形化設計封裝:來源 → 轉換 (腳本/查閱/衍生欄) → 目的地;
  • 支援 COM 程式介面,前端應用可呼叫 Microsoft.SqlServer.Dts.Runtime 執行封裝;
  • 內建資料驗證/轉換(如婚姻狀態 0/1 → Y/N);
  • 效能極高,資料量增長優勢明顯;
  • 缺點:封裝開發/部署較複雜,小專案大材小用。

方案四:異構查詢 OPENROWSET / OPENDATASOURCE

-- OPENROWSET 可在查詢中直接過濾來源
SELECT * INTO LocalDB.dbo.Target
FROM OPENROWSET('MSDASQL', 'DSN=OracleDSN;UID=usr;PWD=pwd', 'SELECT * FROM src_table WHERE status=1');

-- OPENDATASOURCE 僅開連結,後續自行 JOIN/WHERE
SELECT * FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=RemoteSrv;User ID=u;Password=p').RemoteDB.dbo.Table;

差異:OPENROWSET 可在連結字串內帶 WHERE 過濾;OPENDATASOURCE 僅建立連結。缺點:參數複雜、無圖形化驗證、大量資料效能不如 SSIS/BCP。

選型決策樹

  • SQL Server 同構、無清洗、結構同步 → SELECT INTO / INSERT;
  • 需人工清洗/格式轉換、中小量 → Excel/CSV 中轉;
  • 自動化/排程/大量/複雜轉換/前端整合 → SSIS;
  • 臨時/一次性跨異構資料庫查詢 → OPENROWSET。
copyright © 萬盛學電腦網 all rights reserved