萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 數據按類別批量匯出為多個 Excel 檔案:WHILE 迴圈 + BCP 實戰

需求背景

單表數十萬筆,需依 TypeID 分類匯出為數百個 .xls 檔,逐手操作 SSIS 或另存為不可行。

核心方案:動態 SQL + xp_cmdshell + BCP

-- 1. 宣告變數
DECLARE @sql VARCHAR(600), @TypeID INT;

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

-- 3. 迴圈處理每個分類
WHILE EXISTS (SELECT 1 FROM t_TestTable WHERE TypeID = @TypeID)
BEGIN
    -- 3.1 組裝 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(8)) + ') a" ' +
               'QUERYOUT "F:\datafile\TypeData' + CAST(@TypeID AS VARCHAR(8)) + '.xls" ' +
               '-c -q -S"ServerName" -U"sa" -P"Password" -d"DBName"';

    -- 3.2 執行匯出(需先啟用 xp_cmdshell)
    EXEC master..xp_cmdshell @sql;

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

關鍵設定:啟用 xp_cmdshell

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;
-- 使用完畢建議關閉:EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE;

BCP 參數速查

參數說明
-c字元模式(預設欄位分隔符 \t,列分隔符 \r\n)
-q引用識別碼(支援含空格/關鍵字的物件名)
-S伺服器名稱
-U / -P登入帳密
-d資料庫名稱
-t / -r自訂欄位/列分隔符(如 -t, 產生 CSV)

進階替代方案(更安全、可維護)

  • SSIS 專案:Foreach Loop Container 遍歷分類,Flat File Destination 動態設定檔名,部署至 SSISDB 排程執行。
  • PowerShell + dbatools:Invoke-DbaQuery -SqlInstance ... | Export-Csv -Path ... 彈性更高。
  • CLR 預存程序:直接用 .NET 寫入 Excel (EPPlus/ClosedXML),避免 xp_cmdshell 安全風險。
copyright © 萬盛學電腦網 all rights reserved