方法一: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 是最靈活的中介。
流程
- 來源資料庫匯出至 **CSV** (通用) 或 **Excel (.xlsx/.xls)**。
- 用 Excel / Power Query / Python / ETL 工具進行:
- 刪除廢棄列、修正錯誤值、統一日期格式、去除空白、VLOOKUP 補齊代碼表...
- 利用 **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**。
方法選擇決策樹
- 來源、目標皆 SQL Server? → **SELECT INTO / INSERT INTO / BCP / SSIS** (高效、自動化)。
- 來源異構 或 需複雜清洗? → **匯出 CSV/Excel → 清洗 → BULK INSERT / SSIS / OPENROWSET**。
- 一次性小量、人工參與? → **Excel 精靈 / 手工複製貼上**。
- 定期自動化 ETL? → **SSIS 封裝 + SQL Agent 排程** (企業級標準)。