SQL Server 大數據量匯入兩大絕招：SELECT INTO 跨庫直搬 vs Excel 中轉清洗

方法一：SELECT INTO / INSERT INTO 跨資料庫直搬 (同質遷移首選)

適用場景

• 來源與目標皆為 SQL Server (版本相容)。
• 資料結構一致或可映射，無需複雜清洗。
• 網路互通，可建立 Linked Server 或同實例跨庫。

核心語法

-- 1. 建立目標表結構 (僅結構，無資料)
SELECT TOP 0 * INTO TargetDB.dbo.M_Product FROM SourceDB.dbo.Product;

-- 2. 全量匯入
INSERT INTO TargetDB.dbo.M_Product (col1, col2, ...)
SELECT col1, col2, ... FROM SourceDB.dbo.Product;

-- 或一次性建表+匯入 (僅限同實例)
SELECT * INTO TargetDB.dbo.M_Product FROM SourceDB.dbo.Product;

關鍵限制與注意

快速建立空表技巧

-- 僅複製結構 (所有版本)
SELECT TOP 0 * INTO NewTable FROM SourceTable;

-- 或 WHERE 1=0 (標準 SQL)
SELECT * INTO NewTable FROM SourceTable WHERE 1 = 0;

方法二：Excel/CSV 中轉清洗 → 導入 (異構/髒資料首選)

適用場景

• 來源非 SQL Server (Oracle, MySQL, PostgreSQL, DB2, 文字檔...)。
• 資料品質差：髒字元、編碼亂碼、格式不一、需業務規則過濾/轉換。
• 需人工審核、樣本抽查、分批匯入。

標準流程

1. 匯出：來源庫 → CSV (UTF-8 帶 BOM) 或 Excel (.xlsx)。大表分檔 (建議 < 100 萬行/檔)。
2. 清洗 (Excel/Power Query/Python/PowerShell)：
   • 去除前後空白 `TRIM()`
   • 統一日期格式 `YYYY-MM-DD`
   • 替換非法字元、統一編碼
   • 依業務規則過濾/衍生欄位
   • 去重、補齊必填欄位
3. 導入：
   • SSMS「工作」→「匯入資料」嚮導 (選擇 Flat File / Excel 來源)。
   • 或 `BULK INSERT` / `bcp` / `OPENROWSET` 腳本化自動化。
   • 大表建議：先 `BULK INSERT` 進 **Staging Table (Heap, 無索引)** → T-SQL 清洗/轉型 → `INSERT INTO` 正式表 (配合 `TABLOCK`、最小記錄)。

Excel 連結伺服器即時查詢 (臨時分析用)

-- 建立連結 (需安裝 ACE OLEDB Provider)
EXEC sp_addlinkedserver @server = 'EXCEL_LINK',
    @srvproduct = 'Excel',
    @provider = 'Microsoft.ACE.OLEDB.12.0',
    @datasrc = 'C:\Data\Import.xlsx',
    @provstr = 'Excel 12.0 Xml;HDR=YES;IMEX=1';

-- 查詢工作表 (名稱含 $)
SELECT * FROM EXCEL_LINK...[Sheet1$];

-- 清除
EXEC sp_dropserver 'EXCEL_LINK', 'droplogins';

⚠️ **限制**：Excel 檔被開啟時無法查詢；大檔 (>10MB) 查詢極慢；生產自動化**嚴禁**直連 Excel，請先轉 CSV 再 `BULK INSERT`。

方案選型決策表

避坑總結

• 🚫 `SELECT INTO` 目標表已存在 → 用 `INSERT INTO` 或先 `DROP`。
• 🚫 身份欄位衝突 → 匯入後 `DBCC CHECKIDENT ('Table', RESEED)`。
• 🚫 編碼亂碼 → 匯出 CSV 必須 **UTF-8 with BOM**，匯入指定 `CODEPAGE = 65001`。
• 🚫 日期格式錯誤 → 統一 `YYYY-MM-DDTHH:MM:SS` (ISO 8601) 或 `CONVERT` 格式碼 120/121。
• 🚫 大量匯入記錄檔暴漲 → 目標資料庫暫設 `BULK_LOGGED`，匯入後改回 `FULL` 並完整備份。

總結

**同質乾淨用直搬，異構髒資料用中轉**。工具次於流程：**導出 → 清洗 (自動化腳本優於手工) → Staging → 校驗 → 正式表 → 索引重建 → 統計更新 → 業務驗收**。