SQL Server 匯入匯出實戰大全：OPENROWSET、BCP、BULK INSERT 完整指南

前言

資料匯入匯出是資料庫管理與開發的高頻操作。本文彙整 SQL Server 2000 環境下最實用的匯入匯出技術，涵蓋異構資料源存取、大量資料移動、多種檔案格式處理，附帶完整 T-SQL 範例，供實務參考。

一、跨資料庫/異構資料源直接查詢 (OPENROWSET / OPENDATASOURCE)

1. 存取另一台 SQL Server

-- 使用連結伺服器名稱
SELECT * FROM OPENROWSET('SQLOLEDB', 'sprogram'; 'develop'; '11111', 'SELECT * FROM kind')

-- 使用 IP 位址
SELECT * FROM OPENROWSET('SQLOLEDB', '190.1.1.247'; 'develop'; '11111', 'SELECT * FROM kind')

-- 使用 OPENDATASOURCE (適合臨時查詢)
SELECT * FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=sprogram;User ID=develop;Password=11111').new_ks.dbo.kind

2. 存取 Access 2000

-- OPENDATASOURCE 方式
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=e:\north.mdb;User ID=Admin;Password=')...supply

-- OPENROWSET 方式 (查詢整張表)
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'e:\north.mdb'; 'admin'; '', supply)

-- OPENROWSET 方式 (帶 WHERE 條件)
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'e:\north.mdb'; 'admin'; '', 'SELECT * FROM supply WHERE 供應商編號 > 10')

3. 存取 Excel 試算表

-- 查詢工作表 sheet1$ (HDR=YES 表示第一列為標題)
SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0', 'Excel 5.0;HDR=YES;DATABASE=e:\test.xls', sheet1$)

-- 匯入至新資料表
SELECT * INTO 新資料表 FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0', 'Excel 5.0;HDR=YES;DATABASE=e:\test1.xls', sheet3$)

4. 存取其他格式 (dBase、FoxPro)

-- dBase IV
SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0', 'DBase IV;HDR=NO;IMEX=2;DATABASE=C:\', 'SELECT * FROM [客戶資料4.dbf]')

-- dBase III
SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0', 'DBase III;HDR=NO;IMEX=2;DATABASE=C:\', 'SELECT * FROM [客戶資料3.dbf]')

-- FoxPro (透過 MSDASQL + Visual FoxPro ODBC Driver)
SELECT * FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\', 'SELECT * FROM [aa.DBF]')

二、大量資料匯入匯出工具

1. BCP 命令列工具

BCP (Bulk Copy Program) 是高效能的命令列工具，適合大量資料移動。

-- 匯出查詢結果至文字檔
EXEC master..xp_cmdshell 'bcp "SELECT class_no, kind_no, cn_name FROM new_ks.dbo.kind ORDER BY 1,2" queryout "e:\TX1.txt" -c -q -S"sprogram" -U"develop" -P"12345"'

-- 直接匯出整張資料表
EXEC master..xp_cmdshell 'bcp new_ks.dbo.class out "e:\TX3.txt" -c -S"sprogram" -U"develop" -P"12345"'

-- 匯入文字檔至資料表
EXEC master..xp_cmdshell 'bcp "資料庫.dbo.資料表" in c:\DT.txt -c -S伺服器名 -U用戶 -P密碼'
EXEC master..xp_cmdshell 'bcp hdh.dbo.kind in "e:\TX1.txt" -c -q -S -U -P'

-- 注意：BCP 不支援直接匯入 .xls，需先另存為 CSV/文字檔

2. BULK INSERT T-SQL 陳述式

在查詢分析器中執行，需以系統管理員 (sa) 登入，目標資料表需預先存在且欄位數一致。

BULK INSERT hdh.dbo.kind
FROM 'e:\tx1.txt'
WITH (
    FIELDTERMINATOR = '\t',
    ROWTERMINATOR = '\n'
)

-- 使用格式化檔案 (格式檔可由 bcp 產生)
BULK INSERT tbyhhk FROM 'c:\t.txt' WITH (FORMATFILE = 'c:\bcp.txt')

3. 文字檔驅動程式查詢 (MSDASQL)

-- 直接查詢文字檔並匯入
SELECT * INTO 你的資料表 
FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=c:\temp;', 'SELECT * FROM contact.txt')

三、實務建議與注意事項

• 權限設定：xp_cmdshell 需 sysadmin 固定伺服器角色；BULK INSERT 需 ADMINISTER BULK OPERATIONS 或 bulkadmin 角色。
• 效能考量：大量資料優先使用 BCP 或 BULK INSERT，避免 OPENROWSET 逐列處理開銷。
• 編碼問題：中文資料匯出入需注意代碼頁 (Code Page)，BCP 可用 -C 936 (繁體) 或 -C 65001 (UTF-8)。
• 格式化檔：複雜欄位對應建議產生格式化檔 (bcp ... format nul -f fmt.fmt)，確保欄位精確對應。