SQL Server 匯入匯出大全：Excel、文字、DBF、Access、XML、整庫備份一網打盡

SQL Server 資料搬遷、異構資料源整合、備份還原，核心都離不開匯入匯出操作。本文彙整 bcp、OPENROWSET、OPENDataSource、BULK INSERT、sp_xml_preparedocument 等核心技術，涵蓋 Excel、CSV、TXT、DBF、Access、XML、整庫等 10+ 場景，附完整 T-SQL 範例碼，可直接複製調適生產環境使用。

核心工具對照表

高頻場景範例碼

1. 匯出資料表至 Excel（舊版 .xls）

EXEC master..xp_cmdshell 
'bcp "SettleDB.dbo.shanghu" out "c:\temp1.xls" -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""'

關鍵參數：-c 字元模式、-q 引用識別碼、-S 伺服器、-U/-P 認證。

2. 從 Excel 匯入（使用 OPENDataSource）

SELECT * 
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

註：.xlsx 需改用 Microsoft.ACE.OLEDB.12.0 + Extended Properties='Excel 12.0 Xml;HDR=YES'。

3. 匯出至文字檔（逗點分隔 CSV）

EXEC master..xp_cmdshell 
'bcp "Select * from dbname..tablename" queryout "d:\tt.txt" -c -t"," -U sa -P password'

-t"," 指定逗點為欄位分隔符。

4. 從文字檔匯入（BULK INSERT）

BULK INSERT dbname..tablename
FROM 'c:\test.txt'
WITH (
    FIELDTERMINATOR = ';',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2  -- 跳過標題列
)

5. DBF (FoxPro/dBase) 雙向操作

導入：

SELECT * FROM OPENROWSET('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceDB=e:\VFP98\data;SourceType=DBF',
'SELECT * FROM customer WHERE country != "USA" ORDER BY country')

導出至現存 DBF 結構：

INSERT INTO OPENROWSET('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\',
'SELECT * FROM [aa.DBF]')
SELECT * FROM SQL_Source_Table

6. Access (.mdb/.accdb) 雙向操作

導出： INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'x:\A.mdb';'admin';'', A表) SELECT * FROM SQL_DB..B表

導入： INSERT INTO B表 SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'x:\A.mdb';'admin';'', A表)

7. XML 檔案導入關聯表

DECLARE @idoc INT, @doc NVARCHAR(MAX)
SET @doc = N''
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT * FROM OPENXML(@idoc, '/root/Customer/Order', 1)
WITH (oid CHAR(5), amount FLOAT, comment NTEXT 'text()')

EXEC sp_xml_removedocument @idoc

8. 整庫匯出/匯入自動化存儲過程

CREATE PROCEDURE 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
BEGIN
    IF @tbname LIKE '%.%.%'
    BEGIN
        DECLARE @sql VARCHAR(8000) = 'bcp ' + @tbname + 
            CASE @isout WHEN 1 THEN ' out ' ELSE ' in ' END +
            '"' + @filename + '" /w /S ' + @servername +
            CASE WHEN ISNULL(@username,'')='' THEN '' ELSE ' /U ' + @username END +
            ' /P ' + ISNULL(@password,'')
        EXEC master..xp_cmdshell @sql
    END
    ELSE
    BEGIN
        -- 整庫邏輯：游標遍歷 sysobjects 產生每表 bcp 命令
        -- 此處省略完整游標代碼，實際部署請補全
    END
END

調用範例：EXEC File2Table 'localhost','sa','pwd','MyDB..MyTable','C:\backup\MyTable.txt',1（匯出單表）

生產環境部署檢查清單

• [ ] sp_configure 'show advanced options',1; RECONFIGURE; sp_configure 'xp_cmdshell',1; RECONFIGURE;（啟用 xp_cmdshell）
• [ ] sp_configure 'Ad Hoc Distributed Queries',1; RECONFIGURE;（啟用 OPENROWSET）
• [ ] 確認 64 位元 OLEDB 驅動程式已安裝（ACE 12.0/14.0/16.0）
• [ ] SQL Server 服務帳戶具備檔案路徑讀寫權限（網路路徑需網域帳號）
• [ ] 大量匯入前建議：ALTER DATABASE [DB] SET RECOVERY SIMPLE 減少記錄檔膨脹
• [ ] 匯入後執行 UPDATE STATISTICS 重建統計資訊

常見錯誤排查