SQL Server 資料依條件批量匯出多個 Excel 檔案：BCP + WHILE 迴圈實戰

需求情境

單一資料表數十萬筆，需依分類欄位（數百類）各自匯出為獨立 Excel 檔。逐手操作 SSIS 或另存新檔不切實際。

核心技術：xp_cmdshell + BCP + 動態 SQL

-- 1. 啟用 xp_cmdshell（需系統管理員）
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;

-- 2. 批量匯出核心腳本
DECLARE @sql VARCHAR(8000), @TypeID INT;

-- 取得第一個分類 ID
SELECT @TypeID = MIN(TypeID) FROM t_TestTable;

WHILE EXISTS (SELECT 1 FROM t_TestTable WHERE TypeID = @TypeID)
BEGIN
    -- 動態組裝 BCP 指令
    SET @sql = 'bcp "SELECT * FROM (SELECT ' +
               '''Col1'' AS Col1, ' + '''Col2'' AS Col2, ' + '''Col3'' AS Col3 ' +  -- 標題列
               'UNION ALL SELECT Col1, Col2, Col3 FROM t_TestTable WHERE TypeID = ' +
               CAST(@TypeID AS VARCHAR(10)) + ') t" ' +
               'queryout "F:\DataExport\TypeData_' + CAST(@TypeID AS VARCHAR(10)) + '.xls" ' +
               '-c -q -S"ServerName" -U"sa" -P"Password" -d"DBName"';

    -- 執行匯出
    EXEC master..xp_cmdshell @sql;

    -- 取得下一個分類 ID（支援不連續 ID）
    SELECT @TypeID = ISNULL((SELECT MIN(TypeID) FROM t_TestTable WHERE TypeID > @TypeID), @TypeID + 1);
END

關鍵參數說明

進階優化建議

• 改用 SSIS Foreach Loop Container：視覺化、錯誤處理、記錄完整、效能更佳
• 檔案格式選擇：BCP 輸出為 tab 分隔文字檔（.txt/.csv），Excel 可直接開啟；真正的 .xls/.xlsx 需用 SSIS Excel 目的地或 OpenXML SDK
• 並行化：若分類數極大，可拆分多批次平行執行，或使用 PowerShell 背景作業
• 安全性：避免在腳本明文寫入密碼，改用 Windows 驗證（-T）或 Proxy Account

常見坑點

1. BCP 版本需與 SQL Server 版本相容
2. 輸出路徑 SQL Server 服務帳戶需有寫入權限
3. 欄位含換行符/雙引號需妥善逸出，否則 Excel 開啟會亂碼
4. xp_cmdshell 為高風險功能，用畢即關閉：EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE;