單表數十萬筆,需依 TypeID 分類匯出為數百個 .xls 檔,逐手操作 SSIS 或另存為不可行。
-- 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
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;
-- 使用完畢建議關閉:EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE;
| 參數 | 說明 |
|---|---|
-c | 字元模式(預設欄位分隔符 \t,列分隔符 \r\n) |
-q | 引用識別碼(支援含空格/關鍵字的物件名) |
-S | 伺服器名稱 |
-U / -P | 登入帳密 |
-d | 資料庫名稱 |
-t / -r | 自訂欄位/列分隔符(如 -t, 產生 CSV) |
Invoke-DbaQuery -SqlInstance ... | Export-Csv -Path ... 彈性更高。xp_cmdshell 安全風險。