您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2005 大量資料備份與匯入程式設計實戰：BCP 與 OPENROWSET

編輯：關於SqlServer

前言

面對大量資料（GB 級以上）的匯出匯入，傳統 INSERT 陳述式效能極差。本文介紹兩大神器：BCP 公用程式與 OPENROWSET，並提供完整 C# 程式碼範例。

工具一：BCP (Bulk Copy Program)

核心優勢

高效能大量複製，繞過交易記錄大量寫入（配合最小記錄模式）
支援字元/原生/Unicode 多種格式
可指定批次大小、網路封包大小、欄位/列分隔符

常用參數速查

參數	說明
`out`	資料表匯出至檔案
`queryout`	查詢結果匯出至檔案
`in`	檔案匯入資料表
`-c`	字元模式（預設分隔符：Tab/換行）
`-t`	指定欄位分隔符（如 `-t\",\"`）
`-r`	指定列分隔符（如 `-r\"\\n\"`）
`-b`	批次大小（如 `-b5000`）
`-a`	網路封包大小（如 `-a65535`）
`-k`	保留 Null 值而非預設值
`-F`/`-L`	指定起始/結束列號

C# 程式碼：透過 xp_cmdshell 執行 BCP 匯出

public void WriteAllToFile(string fileName)
{
    var cmds = new List();
    
    // 1. 啟用 xp_cmdshell
    cmds.Add("EXEC sp_configure 'show advanced options', 1; RECONFIGURE;");
    cmds.Add("EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;");
    
    // 2. 組裝 BCP 命令
    string[] conn = PubConstant.MessageInfo(); // [Server, DB, User, Pass]
    string bcpCmd = string.Format(
        "EXEC {0}..xp_cmdshell 'bcp {0}.dbo.Department out {1} -c -U {2} -P {3} -S {4}'",
        conn[1], fileName, conn[2], conn[3], conn[0]);
    cmds.Add(bcpCmd);
    
    // 3. 關閉 xp_cmdshell
    cmds.Add("EXEC sp_configure 'show advanced options', 1; RECONFIGURE;");
    cmds.Add("EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE;");
    
    // 4. 依序執行
    foreach (var cmd in cmds) DbHelperSQL.ExecuteSql(cmd);
}

BCP 常見問題與對策

Null 值處理：使用 -k 參數保留 Null，或在查詢中用 ISNULL/COALESCE 替換
命令長度限制 (1023 bytes)：複雜查詢超過限制時，改用視圖、暫存表或格式化檔案
xp_cmdshell 安全性：僅在必要時啟用，用畢立即關閉；生產環境建議使用 SQLCLR 或 SSIS 替代

工具二：OPENROWSET

適用場景

直接讀取 Access、Excel、文字檔、其他 SQL Server 實例
無需建立連結伺服器即可進行分散式查詢
適合 ETL 臨時資料載入

C# 程式碼：讀取 Access 資料庫

public void ReadFromAccess(string fileName)
{
    var cmds = new List();
    
    // 啟用 Ad Hoc Distributed Queries
    cmds.Add("EXEC sp_configure 'show advanced options', 1; RECONFIGURE;");
    cmds.Add("EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;");
    
    // OPENROWSET 查詢
    string query = @"SELECT CustomerID, CompanyName 
                     FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                     'C:\testFile\Northwind.mdb';'admin';'', Customers)";
    cmds.Add(query);
    
    // 關閉選項
    cmds.Add("EXEC sp_configure 'Ad Hoc Distributed Queries', 0; RECONFIGURE;");
    cmds.Add("EXEC sp_configure 'show advanced options', 0; RECONFIGURE;");
    
    foreach (var cmd in cmds) DbHelperSQL.ExecuteSql(cmd);
}

常用 OLE DB Provider 字串

Access：'Microsoft.Jet.OLEDB.4.0' 或 'Microsoft.ACE.OLEDB.12.0' (Access 2007+)
Excel：'Microsoft.ACE.OLEDB.12.0';'Excel 12.0;HDR=YES;IMEX=1'
SQL Server：'SQLNCLI';'Server=IP;Database=DB;UID=user;PWD=pass'