SQL Server 匯入匯出大全：BCP、OPENROWSET、OPENDATASOURCE、DTS、XML 完整語法速查手冊

SQL Server 跨異質資料源匯入匯出 (Excel、文字檔、DBF、Access、XML、資料庫間) 最全語法彙總，含 bcp、OPENROWSET、OPENDATASOURCE、BULK INSERT、DTS 套件、XML 大量載入、二進位檔案處理預存程序。

一、 BCP 命令列工具 (最高效能、適合自動化腳本)

匯出到文字檔

bcp "dbname..tablename" out "c:\data.txt" -c -t, -T -S servername
bcp "SELECT * FROM dbname..tablename" queryout "c:\data.txt" -c -t, -T -S servername

• -c 字元模式、-t, 欄位分隔逗號、-T 信任連線、-w Unicode (雙位元組)

匯入文字檔

bcp "dbname..tablename" in "c:\data.txt" -c -t, -T -S servername

匯出 Excel (舊技巧，實際產生 CSV 再改副檔名)

bcp "dbname..tablename" out "c:\temp.xls" -c -q -T -S servername

二、 OPENROWSET / OPENDATASOURCE (Ad-hoc 分散式查詢，免建立連結伺服器)

Excel 匯入

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
    'Data Source="c:\test.xls";Extended Properties=Excel 8.0')...[Sheet1$]

Excel 匯出 (需預建檔案與標題列)

INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
    'Data Source="c:\out.xls";Extended Properties=Excel 8.0')...[Sheet1$]
SELECT * FROM dbname..tablename

文字檔 (指定分隔符)

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Text;HDR=YES;FMT=Delimited;Database=c:\', 'SELECT * FROM data.txt')

DBF (FoxPro/dBase)

-- 匯入
SELECT * FROM OPENROWSET('MSDASQL',
    'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\', 'SELECT * FROM table.dbf')

-- 匯出 (需預建 DBF 結構)
INSERT INTO OPENROWSET('MSDASQL',
    'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\', 'SELECT * FROM table.dbf')
SELECT * FROM dbname..tablename

Access

-- 匯入
INSERT INTO dbname..tablename
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=c:\a.mdb')...TableA

-- 匯出
INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=c:\a.mdb')...TableA
SELECT * FROM dbname..tablename

三、 BULK INSERT (T-SQL 原生，效能接近 bcp，可用於預存程序)

BULK INSERT dbname..tablename
FROM 'c:\data.txt'
WITH (FIELDTERMINATOR = ';', ROWTERMINATOR = '\n', FIRSTROW = 2);

四、 XML 匯入 (SQLXML Bulk Load / OPENXML)

OPENXML 範例

DECLARE @idoc INT, @xml NVARCHAR(MAX);
SET @xml = N'';
EXEC sp_xml_preparedocument @idoc OUTPUT, @xml;
SELECT * FROM OPENXML(@idoc, '/ROOT/CUSTOMER/ORDER', 2)
    WITH (oid CHAR(5), amount FLOAT);
EXEC sp_xml_removedocument @idoc;

五、 完整資料庫匯出/匯入預存程序 (bcp 迴圈全庫)

CREATE PROC File2Table
    @servername VARCHAR(200), @username VARCHAR(200), @password VARCHAR(200),
    @tbname VARCHAR(500),      -- 'db.dbo.table' 或 'db' (全庫)
    @filename VARCHAR(1000),   -- 檔案/目錄
    @isout BIT                 -- 1=匯出, 0=匯入
AS
-- 略 (原文含完整游標迴圈邏輯，自動處理全庫所有使用者表)
GO

呼叫範例

-- 匯出單表
EXEC File2Table 'server', 'sa', 'pwd', 'db.dbo.table', 'c:\table.txt', 1
-- 匯出全庫 (每表一檔)
EXEC File2Table 'server', 'sa', 'pwd', 'db', 'c:\export_dir\', 1

六、 二進位檔案 (image/text/ntext) 匯入匯出專用預存程序

利用 bcp 格式檔 (/i 參數) 保留二進位完整性，支援大欄位分塊處理。原文含 p_binaryIO 完整碼 (略)。

七、 快速選型指南