萬盛學電腦網

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

SQL Server 大量匯入資料的兩大經典方法:SELECT INTO 與 Excel 中介法

方法一:SELECT INTO / INSERT INTO (同質資料庫間)

適用於 **SQL Server 到 SQL Server** (跨版本相容),單純資料搬移,結構已存在或需建立新表。

SELECT INTO:建立新表並複製資料

-- 於目標資料庫執行
SELECT * INTO NewTable FROM SourceServer.SourceDB.dbo.SourceTable;
  • **優點**:極簡單、自動建立表結構 (含 Identity)、最小記錄 (Bulk Logged 復原模式下)。
  • **缺點**:**不複製索引、條件約束、觸發器、權限**;目標表不可預先存在;不支援跨異構資料來源 (Oracle、MySQL 等)。

INSERT INTO:追加資料至既有表

INSERT INTO TargetTable (col1, col2, ...)
SELECT col1, col2, ... FROM SourceServer.SourceDB.dbo.SourceTable;
  • 支援**資料合併** (目標表已有資料)。
  • 需目標表結構相容 (資料類型隱含轉換允許,如 `int` → `float`)。
  • 可搭配 `WHERE`、`JOIN` 過濾/轉換。

快速建立空表結構 (不複製資料)

-- 僅複製結構 (含 Identity、Nullability、Default)
SELECT * INTO NewTable FROM SourceTable WHERE 1 <> 1;
-- 或
SELECT TOP 0 * INTO NewTable FROM SourceTable;

方法二:Excel/CSV 作為中介工具 (異構、需清洗)

當來源為 **Oracle、DB2、MySQL、文字檔、舊系統匯出** 或資料**品質不佳** (需人工/規則清洗) 時,Excel/CSV 是最靈活的中介。

流程

  1. 來源資料庫匯出至 **CSV** (通用) 或 **Excel (.xlsx/.xls)**。
  2. 用 Excel / Power Query / Python / ETL 工具進行:
    • 刪除廢棄列、修正錯誤值、統一日期格式、去除空白、VLOOKUP 補齊代碼表...
  3. 利用 **SQL Server 匯入/匯出精靈** (SSIS 封裝) 或 `OPENROWSET`/`OPENDATASOURCE` 直接讀入。

T-SQL 直接讀取 Excel (Ad Hoc)

-- 1. 建立連結伺服器 (一次性)
EXEC sp_addlinkedserver
    @server = 'EXCEL_LINK',
    @srvproduct = 'Excel',
    @provider = 'Microsoft.ACE.OLEDB.12.0',  -- .xls 用 Microsoft.Jet.OLEDB.4.0
    @datasrc = 'C:\Data\Import.xlsx',
    @provstr = 'Excel 12.0 Xml;HDR=YES';      -- HDR=YES 第一列為標題

EXEC sp_addlinkedsrvlogin 'EXCEL_LINK', 'false', 'sa', 'Admin', NULL;

-- 2. 查詢/匯入
SELECT * INTO TargetTable FROM EXCEL_LINK...[Sheet1$];  -- 工作表名加 $ 並用 [] 包裹

-- 3. 清理
EXEC sp_dropserver 'EXCEL_LINK', 'droplogins';

Excel 匯入實戰提醒

  • **資料類型推斷**:Excel 驅動程式掃描前 8 行 (預設 `TypeGuessRows=8`),混合型會誤判。建議註冊表調大或預先統一格式。
  • **前置零/長數字**:設定欄位格式為「文字」,或在連接字串加 `IMEX=1` 強制視為文字。
  • **檔案鎖定**:匯入時 **關閉 Excel**,否則獨佔鎖定導致失敗。
  • **大數據量** (>100萬列) Excel 開啟/儲存極慢,**首選 CSV + BCP / BULK INSERT / SSIS**。

方法選擇決策樹

  1. 來源、目標皆 SQL Server? → **SELECT INTO / INSERT INTO / BCP / SSIS** (高效、自動化)。
  2. 來源異構 或 需複雜清洗? → **匯出 CSV/Excel → 清洗 → BULK INSERT / SSIS / OPENROWSET**。
  3. 一次性小量、人工參與? → **Excel 精靈 / 手工複製貼上**。
  4. 定期自動化 ETL? → **SSIS 封裝 + SQL Agent 排程** (企業級標準)。
copyright © 萬盛學電腦網 all rights reserved