一、SQL Server 之間
SELECT INTO (建表+匯入)
-- 同資料庫
SELECT * INTO Table2 FROM Table1;
-- 跨資料庫 (需建表權限)
USE DB1;
SELECT * INTO DB2.dbo.Table2 FROM Table1;
INSERT INTO (追加/更新)
-- 追加不重複主鍵
INSERT INTO Table1
SELECT * FROM Table2 t2
WHERE NOT EXISTS (SELECT 1 FROM Table1 t1 WHERE t1.PK = t2.PK);
-- 更新既有
UPDATE t1 SET t1.Col1 = t2.Col1, t1.Col2 = t2.Col2
FROM Table1 t1 JOIN Table2 t2 ON t1.PK = t2.PK;
-- Upsert 合併 (SQL 2008+ MERGE 更佳)
二、OPENDATASOURCE / OPENROWSET (Ad Hoc 分散式查詢)
啟用需求：sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;
語法差異
| 函數 | 適用場景 | 語法 |
|---|
OPENDATASOURCE | 只需指定資料來源，後續像操作本地表一樣用四部分名稱 | OPENDATASOURCE(provider, conn_str).Catalog.Schema.Table |
OPENROWSET | 一次性查詢，可在函數內直接寫 SELECT 清單 | OPENROWSET(provider, conn_str, 'SELECT ... FROM Table') |
常見 Provider 連接字串
| 來源 | Provider | 連接字串範例 |
|---|
| SQL Server | SQLOLEDB | Data Source=192.168.1.100;User ID=sa;Password=xxx |
| SQL Server (ACE) | MSOLEDBSQL / SQLNCLI11 | Server=192.168.1.100;UID=sa;PWD=xxx |
| Access (.mdb) | Microsoft.Jet.OLEDB.4.0 | Data Source=C:\Data.mdb;Persist Security Info=False |
| Access (.accdb) | Microsoft.ACE.OLEDB.12.0 | Data Source=C:\Data.accdb;Persist Security Info=False |
| Excel (.xls) | Microsoft.Jet.OLEDB.4.0 | Data Source=C:\Book.xls;Extended Properties='Excel 8.0;HDR=YES' |
| Excel (.xlsx) | Microsoft.ACE.OLEDB.12.0 | Data Source=C:\Book.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES' |
| Text/CSV | Microsoft.Jet.OLEDB.4.0 | Data Source=C:\Folder\;Extended Properties='Text;HDR=YES' |
| DBF (dBase/FoxPro) | Microsoft.Jet.OLEDB.4.0 | Data Source=C:\Folder\;Extended Properties='dBASE IV;HDR=NO' |
實戰範例
-- 1. SQL Server → SQL Server 匯入
SELECT * INTO LocalTable
FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=192.168.1.100;UID=sa;PWD=xxx').RemoteDB.dbo.RemoteTable;
-- 2. SQL Server → SQL Server 匯出
INSERT INTO OPENDATASOURCE('SQLOLEDB', 'Data Source=192.168.1.100;UID=sa;PWD=xxx').RemoteDB.dbo.RemoteTable
SELECT * FROM LocalTable;
-- 3. Access → SQL Server
SELECT * INTO SqlTable
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\Data.mdb;', 'SELECT * FROM AccessTable');
-- 4. SQL Server → Excel (需先建好 Sheet 標題列)
INSERT INTO OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=C:\Report.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES"')...[Sheet1$]
SELECT * FROM SqlTable;
-- 5. CSV → SQL Server (Text 驅動)
SELECT * INTO CsvTable
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Text;Database=C:\Folder\;HDR=YES', 'SELECT * FROM [data.csv]');
-- 6. 異構間直轉 (Access → Excel)
INSERT INTO OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=C:\Out.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES"')...[Sheet1$]
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\In.mdb;', 'SELECT * FROM Table1');
三、連結伺服器
建立
EXEC sp_addlinkedserver
@server = 'LK_Access',
@srvproduct = 'Access',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@datasrc = 'C:\Data.mdb';
-- 對應登入 (如需特定帳號)
EXEC sp_addlinkedsrvlogin 'LK_Access', FALSE, NULL, 'Admin', '';
使用
SELECT * FROM LK_Access...Table1; -- 四部分名稱：Server.Catalog.Schema.Table
SELECT * INTO LocalCopy FROM LK_Access...Table1;
移除
EXEC sp_dropserver 'LK_Access', 'droplogins';
四、效能與安全建議
- 大量資料優先使用 SSIS / BCP / BULK INSERT，效能遠優於 Ad Hoc 查詢。
OPENROWSET 的 BULK 選項可直接大量載入文字檔：OPENROWSET(BULK 'C:\data.csv', FORMATFILE='C:\fmt.fmt')。- Excel 匯入長文字欄位必須設定
TypeGuessRows = 0 (見文章 83913)。
- 生產環境關閉 Ad Hoc Distributed Queries，改用連結伺服器或 SSIS 封裝，減少攻擊面。
- 64 位元環境務必安裝 64 位元 Provider (MSOLEDBSQL, ACE 12.0+)，32 位元 Jet/OLEDB 不支援。
