SQL Server 匯入匯出大全：BCP、OPENROWSET、OPENDATASOURCE、BULK INSERT 完整語法速查

彙整 SQL Server 與 Excel、文字檔、DBF、FoxPro、Access、Oracle 等異構資料源雙向匯入匯出的標準 T-SQL 語法與命令列工具。

1. BCP 命令列工具 (最高效、支援大量資料)

bcp "SELECT * FROM DB.dbo.Table" queryout "D:\Export\Data.txt" -c -t, -T -S ServerName


bcp DB.dbo.Table out "D:\Export\Data.xls" -c -q -T -S ServerName


bcp DB.dbo.Table in "D:\Import\Data.txt" -c -t, -T -S ServerName

2. OPENROWSET / OPENDATASOURCE (Ad Hoc 分散式查詢)

SELECT * FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;Database=D:\Data.xlsx;HDR=YES',
    'SELECT * FROM [Sheet1$]'
);


SELECT * FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Text;Database=D:\Import\;HDR=YES',
    'SELECT * FROM [Data.txt]'
);


SELECT * FROM OPENROWSET(
    'MSDASQL',
    'Driver=Microsoft Visual FoxPro Driver;SourceDB=D:\Data;SourceType=DBF',
    'SELECT * FROM Customer'
);


INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'D:\Target.accdb;', 'TableName')
SELECT * FROM SourceTable;

3. BULK INSERT (T-SQL 內建、高效能)

BULK INSERT DB.dbo.Table
FROM 'D:\Import\Data.txt'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2,          -- 跳過標題列
    CODEPAGE = '65001',    -- UTF-8
    TABLOCK                -- 鎖定表提升效能
);

4. 匯入/匯出精靈 (SSMS GUI) / SSIS 封裝

• 適用一次性、視覺化、需轉換邏輯的中小型任務。
• 可另存為 SSIS 封裝 (.dtsx) 排程自動化。

權限與設定前置作業

• sp_configure 'show advanced options', 1; RECONFIGURE;
• sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE; (OPENROWSET/OPENDATASOURCE 需要)
• sp_configure 'xp_cmdshell', 1; RECONFIGURE; (BCP 命令列需要)
• 目標資料夾需授予 SQL Server 服務帳號讀寫權限。

結語

BCP 適合大量高效能批次；OPENROWSET 適合 Ad Hoc 異構查詢；BULK INSERT 適合 T-SQL 內建批次載入；SSIS 適合複雜 ETL 流程。依資料量、頻率、轉換複雜度選擇最適工具。