SQL Server CSV 匯出實戰：SQLCMD、BCP、SSIS 怎麼選怎麼調

老闆丟一句「這個報表，越快越好」，你打開資料表一看——好幾千萬筆。這時候用對工具跟用錯工具，差的不是幾分鐘，是幾個小時。

SQL Server 匯出 CSV 最常用的三個工具是 SQLCMD、BCP、SSIS。網路上很多範例其實是把它們的角色搞混的：最典型的就是把 bcp 命令當成 T-SQL 字串丟進 sp_executesql，抄回去一跑就語法錯誤。這篇把每個工具的定位講清楚，範例全部以可實際執行為前提，並附上分批匯出大型資料的正確寫法。

三個工具，三種角色

先把定位搞對，後面才不會誤用。

BCP（Bulk Copy Program）是獨立的命令列工具，走大量複製 API，匯出大表速度最快、記憶體佔用低。它不是 T-SQL，是在作業系統的命令提示字元（或透過 xp_cmdshell）執行的 .exe。

SQLCMD 也是命令列工具，主要拿來跑 .sql 指令稿、做自動化排程。它本身不是高效能匯出引擎——真要快速匯出大量資料，多半還是在 SQLCMD 指令稿裡呼叫 BCP。

SSIS（SQL Server Integration Services）是視覺化 ETL 平台，強在「匯出過程中要做轉換」：欄位對應、編碼轉換、異常過濾、條件分流。代價是設定與維護成本高。

工具	角色	強項	弱項
BCP	命令列匯出引擎	大量資料最快、資源佔用低	彈性低、特殊處理麻煩
SQLCMD	指令稿 / 排程外殼	自動化、整合方便	不是匯出引擎，常需搭 BCP
SSIS	視覺化 ETL	複雜轉換、流程整合	設定複雜、維護成本高

效能上，純匯出大表 BCP 通常領先，SSIS 因為多了緩衝與轉換管線，純匯出反而比 BCP 慢；但一旦匯出途中需要做轉換，SSIS 一次到位的價值就出來了。實際數字會被磁碟 I/O、網路頻寬、資料表結構、索引拖很大，建議在自己環境跑小樣本實測，不要照搬別人的秒數。

BCP 實戰

BCP 是這三者裡最該先學會的，因為大型匯出最後幾乎都會落到它身上。

基本匯出語法

在命令提示字元（不是 SSMS 的查詢視窗）執行：

1	bcp "SELECT Column1, Column2, Column3 FROM YourDB.dbo.YourTable" queryout "D:\Export\output.csv" -c -t"," -S YourServer -T

關鍵旗標：

queryout：表示來源是一段查詢（如果是整張表用 out）。
-c：字元格式輸出。
-t","：欄位分隔符號設為逗號。
-S：伺服器名稱。
-T：使用 Windows 整合驗證；要用 SQL 帳密就改成 -U 帳號 -P 密碼。

-T 跟 -U/-P 擇一，不要同時給。

效能相關旗標

1	bcp "SELECT * FROM YourDB.dbo.YourTable" queryout "D:\Export\output.csv" -c -t"," -S YourServer -T -b 10000 -a 16384

-b 10000：每批次列數，影響交易與記憶體配置。
-a 16384：網路封包大小（位元組），跨網段匯出時調大有機會減少往返。

這裡要點名一個網路上常見的幻覺旗標：**bcp 沒有 -O 這個選項**。保留識別欄位（identity）值的旗標是 -E，而且 -E 只在「匯入（in）」時才有意義——把資料檔裡的 identity 值原樣寫回去；對 queryout 匯出根本用不到。如果你看到範例寫 -O -- 保持身份值，那是錯的，照抄會直接報錯。至於 -h "TABLOCK" 這類載入提示，同樣是匯入時的最佳化，匯出時不適用。

在 T-SQL 裡呼叫 BCP：用 xp_cmdshell，不是 sp_executesql

這是全篇最重要的一條。很多範例長這樣，看起來很合理，但跑不動：

1
2
3

-- 錯誤示範：sp_executesql 只能執行 T-SQL，不能執行命令列工具
DECLARE @sql nvarchar(max) = 'bcp "SELECT ..." queryout "D:\Export\out.csv" -c -t"," -T -S MyServer';
EXEC sp_executesql @sql;   -- 直接語法錯誤

sp_executesql 的工作是「動態執行一段 T-SQL」，它眼裡 bcp 不是合法的 T-SQL 關鍵字，會立刻報錯。要從 T-SQL 觸發外部的 bcp.exe，唯一的內建途徑是 xp_cmdshell（預設停用，需由 sysadmin 啟用，本身有資安風險，動之前先確認環境政策）：

1
2
3

-- 正確：用 xp_cmdshell 呼叫外部 bcp
EXEC master..xp_cmdshell
  'bcp "SELECT Column1, Column2 FROM MyDB.dbo.MyTable" queryout "D:\Export\out.csv" -c -t"," -T -S MyServer';

啟用 xp_cmdshell（需要時才開，用完建議關掉）：

1 2	EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;

大型資料分批匯出

幾千萬筆一次匯出，記憶體與失敗重試都是麻煩，分批比較穩。重點是分批的邊界要組進實際送給 bcp 的命令字串裡——因為 bcp 啟動的是獨立的新連線，看不到你在外層 T-SQL 宣告的變數。換句話說，你不能寫 WHERE ID BETWEEN @StartID AND @EndID 然後期待 bcp 認得 @StartID；得在組命令時就把數值字串化拼進去。

純命令列的批次檔做法最直接，迴圈變數在拼字串時就展開了：

@echo off
setlocal enabledelayedexpansion

set SERVER=YourServer
set DATABASE=YourDB
set TABLE=YourTable
set EXPORTPATH=D:\Export
set BATCHSIZE=1000000

rem 分十批匯出，邊界值在拼接時就寫進查詢字串
for /L %%i in (1,1,10) do (
    set /a START=((%%i-1)*%BATCHSIZE%)+1
    set /a END=%%i*%BATCHSIZE%

    bcp "SELECT * FROM %DATABASE%.dbo.%TABLE% WHERE ID BETWEEN !START! AND !END! ORDER BY ID" queryout "%EXPORTPATH%\Part_%%i.csv" -c -t"," -S %SERVER% -T
)

如果非得從 T-SQL 驅動，就在 WHILE 迴圈裡用字串串接組出每一批的命令，再交給 xp_cmdshell：

DECLARE @StartID  bigint = 1;
DECLARE @ChunkSize bigint = 1000000;
DECLARE @MaxID    bigint = (SELECT MAX(ID) FROM dbo.Transactions);
DECLARE @cmd      nvarchar(4000);

WHILE @StartID <= @MaxID
BEGIN
    DECLARE @EndID bigint = @StartID + @ChunkSize - 1;

    -- 把邊界值字串化拼進命令，bcp 子行程才看得到
    SET @cmd =
        'bcp "SELECT * FROM YourDB.dbo.Transactions '
      + 'WHERE ID BETWEEN ' + CAST(@StartID AS varchar(20))
      + ' AND ' + CAST(@EndID AS varchar(20)) + ' ORDER BY ID" '
      + 'queryout "D:\Export\Chunk_' + CAST(@StartID AS varchar(20)) + '.csv" '
      + '-c -t"," -T -S YourServer';

    EXEC master..xp_cmdshell @cmd;

    SET @StartID = @EndID + 1;
END

注意這裡是用 WHERE ID BETWEEN n AND n 的「ID 值區間」切批，而不是 ROW_NUMBER()。常見的錯誤寫法是先用 WHERE ID % ChunkSize = 0 過濾、再對 ROW_NUMBER() 套 LEAD：那會在過濾後重新編號成 1、2、3…，跟真正的 ID 邊界完全脫鉤，而且還假設 ID 連續無缺口。只要有刪除或 identity 跳號，切出來的區間就會漏資料或重疊。用 ID 值區間配合 ORDER BY ID，缺口頂多讓某批筆數少一點，不會漏、不會重。

真要平行，靠作業系統排程而非 dm_exec_requests

想同時跑多個 BCP 加速，別用下面這種「查 sys.dm_exec_requests 看有幾個 BCP 在跑」來節流：

1
2
3

-- 無效：外部 bcp 行程不會以 command='BCP' 出現在這裡
WHILE (SELECT COUNT(*) FROM sys.dm_exec_requests WHERE command = 'BCP') >= 4
    WAITFOR DELAY '00:00:01';

bcp 是 SQL Server 外部的作業系統行程，它在引擎內只會以一般的 SELECT 連線出現，command 欄位永遠不會是 'BCP'，這個計數永遠回 0，節流形同虛設。而且 xp_cmdshell 是同步阻塞的——一次只會等一個 bcp 跑完才回來，根本不會有多個並行。真要平行，做法是在作業系統層各自啟動多個 bcp 行程（例如多個排程工作、批次檔背景啟動、或 PowerShell 的 Start-Job），讓 OS 去調度，而不是在 T-SQL 裡自己數。

SQLCMD 實戰

SQLCMD 的價值在指令稿化與排程，不在匯出效能本身。

指令稿變數

:setvar OutputPath "D:\Exports"
:setvar FileName "ExportData.csv"

:on error exit
SET NOCOUNT ON;

要記得 :setvar 只是宣告 sqlcmd 指令稿層級的變數，本身不會對 SQL Server 做任何設定。它能在後續用 $(OutputPath) 這種 $() 語法被展開——但這套展開只在 sqlcmd 環境裡有效。把 $(StartID) 寫進一段準備交給 bcp 的字串、又不是在 sqlcmd 跑，它不會展開，會原樣留著變成壞檔名。

實際匯出仍是在指令稿裡呼叫 bcp（透過命令列或 xp_cmdshell），SQLCMD 負責把流程串起來、接排程。

別把 -m 當記憶體參數

網路上反覆出現一個錯誤，要特別澄清：

1 2	# 錯誤：-m 不是設定記憶體 sqlcmd -S ServerName -b -m 65535 -U DBAdmin -P Password

sqlcmd -m 是「錯誤訊息嚴重性等級門檻」（error level），只有嚴重性大於等於這個值的訊息才會輸出。它的有效範圍是 -1 到 25，寫 65535 直接超出範圍。-m -1 代表連資訊性訊息都印出來。它跟記憶體一點關係都沒有——SQL Server 的記憶體上限是用 sp_configure 'max server memory' 設定的，命令列工具根本沒有控制伺服器記憶體的旗標。

-b 才是有用的：遇到錯誤時讓 sqlcmd 以非零代碼結束批次，方便排程判斷成敗。

CSV 特殊字元跳脫

匯出資料含逗號、雙引號、換行時，需要做 CSV 跳脫。這段是純資料層的 T-SQL 邏輯，可以照用：

CREATE FUNCTION dbo.EscapeCSV (@text nvarchar(max))
RETURNS nvarchar(max)
AS
BEGIN
    IF @text IS NULL RETURN NULL;

    -- 雙引號要變成兩個雙引號
    SET @text = REPLACE(@text, '"', '""');

    -- 含逗號、引號或換行時，整欄用雙引號包起來
    IF @text LIKE '%[",' + CHAR(13) + CHAR(10) + ']%'
        SET @text = '"' + @text + '"';

    RETURN @text;
END;

SSIS 實戰

SSIS 適合匯出途中要轉換的場景。它的設定主要在圖形介面拉元件，這裡只點幾個容易抄錯的地方。

緩衝區屬性屬於資料流程

效能調校常碰到這幾個屬性，但它們是資料流程工作（Data Flow Task）的屬性，在設計工具的屬性視窗設定，不是隨便在某段腳本裡賦值的變數：

DefaultBufferMaxRows：每個緩衝區的最大列數（預設 10000）。
DefaultBufferSize：每個緩衝區的位元組大小（預設約 10 MB）。
BufferTempStoragePath：緩衝溢出時的暫存路徑。

來源查詢可以加提示降低鎖定影響、控制平行度：

1
2
3

SELECT *
FROM YourTable WITH (NOLOCK)   -- 注意 NOLOCK 可能讀到未認可資料，依需求斟酌
OPTION (MAXDOP 4);

Script Task 是 C#，別混 T-SQL 註解

如果在 Script Task 寫程式，那是 C#（或 VB.NET），語法要照 C# 來：註解用 // 不是 --、迴圈變數要有型別、DefaultBufferMaxRows 這類資料流屬性也不能在 Script Task 裡直接賦值。網路上那種 public void Main() 裡塞 -- 註解 又寫 foreach (row in Source) 的範例是拼湊的偽程式碼，根本不能編譯。Script Task 真正會做的是讀寫變數、呼叫 .NET API，例如：

public void Main()
{
    string sourceFile = Dts.Variables["User::SourceFile"].Value.ToString();
    // ... 用 .NET 讀檔、處理、寫出 ...
    Dts.TaskResult = (int)ScriptResults.Success;
}

PowerShell 部署：DeployProject 的簽章

把專案部署到 SSISDB Catalog 時，DeployProject 的參數常被寫錯。它的簽章是 DeployProject(專案名稱, ispac 的位元組陣列)——第二個參數是 .ispac 檔讀進來的 byte array，不是「檔案路徑、專案名稱、說明」三個字串。而且要先取得目標資料夾物件（$folder），再對它呼叫：

# 載入組件
Add-Type -AssemblyName "Microsoft.SqlServer.Management.IntegrationServices"

$server  = New-Object Microsoft.SqlServer.Management.Smo.Server "YourServer"
$catalog = $server.IntegrationServices.Catalogs["SSISDB"]
$folder  = $catalog.Folders["YourFolder"]

# 把 .ispac 讀成位元組陣列
$ispac = [System.IO.File]::ReadAllBytes("D:\SSIS\YourProject.ispac")

# 正確簽章：DeployProject(名稱, byte[])
$folder.DeployProject("YourProject", $ispac)

常見錯誤版本只建了 $catalog 就直接 $folder.DeployProject(路徑, 名稱, 說明)，$folder 是 null、參數也對不上，兩處都會炸。

編碼：UTF-8 沒那麼單純

匯出 UTF-8 CSV 常見寫法是 -C 65001，但有幾個坑要先知道：

1	bcp "SELECT * FROM YourDB.dbo.YourTable" queryout "D:\Export\out.csv" -c -C 65001 -t"," -S YourServer -T

UTF-8 的 code page 支援從 SQL Server 2014 SP2 才加入，更舊的版本沒有。
UTF-8 是 8 位元編碼，要搭 -c（字元格式）使用，不要用 -w（-w 是 UTF-16 寬字元）。
bcp 輸出的 UTF-8 檔不含 BOM，某些下游程式（特別是 Excel 直接開）可能因此判錯編碼把中文變亂碼。

所以單寫 -C 65001 不保證拿到下游都吃得乾淨的 UTF-8。版本不夠新、或下游要 BOM 的話，得另外處理。不確定環境支援度時，先在小樣本上驗證再放上生產。

怎麼選

把工具對到任務上，大致是這樣：

純匯出、量大：BCP。簡單結構、追求速度，沒對手。
要排程、要串流程：SQLCMD 當外殼，內部呼叫 BCP。
匯出途中要轉換：SSIS。欄位對應、編碼轉換、異常過濾一次到位。
大量資料 + 複雜轉換：BCP 先快速倒出原始檔，再用 SSIS 做轉換，分兩階段比硬塞單一工具穩。

監控匯出狀態

匯出進行中，引擎側可以看實際在跑的查詢（注意：這裡看到的是 bcp 背後的 SELECT 連線，不是 command='BCP'）：

SELECT r.session_id, r.start_time, r.status, r.command,
       r.cpu_time, r.total_elapsed_time, r.reads, r.writes, r.logical_reads
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE t.text LIKE '%YourTable%';

收尾

三個工具裡，BCP 值得最先投資——大型匯出最後幾乎都繞回它。而最該避開的不是哪個工具不好，是把它們的角色搞混：bcp 是命令列工具不是 T-SQL，要從資料庫裡觸發就用 xp_cmdshell；分批的邊界得拼進命令字串，因為 bcp 看不到你的 T-SQL 變數；sqlcmd -m 是錯誤等級不是記憶體。把這幾條釐清，你寫出來的匯出腳本才是能真的跑起來的腳本，而不是看起來很專業、一執行就報錯的範本。