SQL Server 利用 BCP 命令與存儲過程實現查詢結果導出文本文件

背景

需將 SQL 查詢結果定時導出為 CSV/文本文件，供下游系統消費。直接在應用層查詢再寫入文件效率低、耦合重；利用 SQL Server 原生 bcp 工具配合 xp_cmdshell，可在資料庫層完成「查詢 → 導出」閉環。

一、BCP 核心參數速查

二、通用導出存儲過程 sp_sql_query_to_file

CREATE PROCEDURE dbo.sp_sql_query_to_file
    @servername   VARCHAR(50),   -- 實例名
    @username     VARCHAR(50),   -- 空字串表信任連線
    @password     VARCHAR(50),
    @sql_query    VARCHAR(2000), -- 查詢語句，單引號需雙寫
    @filePath_name VARCHAR(1000),-- 輸出路徑（不含副檔名）
    @date         DATETIME       -- 日期戳
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @cmd VARCHAR(8000);
    SET @cmd = 'bcp "' + @sql_query + '" queryout "'
             + @filePath_name + '_' + CONVERT(VARCHAR(12), @date, 112) + '.txt"'
             + ' -t "," -w'                 -- CSV + Unicode
             + ' -S ' + @servername
             + CASE WHEN ISNULL(@username,'') = '' THEN '' ELSE ' -U ' + @username END
             + ' -P ' + ISNULL(@password,'');

    EXEC master..xp_cmdshell @cmd;
    IF @@ERROR <> 0
        PRINT '導出失敗，請檢查錯誤碼：' + CAST(@@ERROR AS VARCHAR);
END;

三、前置條件與安全

1. 啟用 xp_cmdshell：

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

2. 權限最小化：僅授予執行該存儲過程的帳號 EXECUTE 權限，不開放直接呼叫 xp_cmdshell。
3. 路徑權限：SQL Server 服務帳號需對輸出目錄具寫入權限。
4. 防注入：@sql_query 由內部組裝，嚴禁直接拼接用戶輸入；若需動態條件，請在過程內參數化構建。

四、調用範例

EXEC dbo.sp_sql_query_to_file
    @servername = 'localhost',
    @username = '',
    @password = '',
    @sql_query = 'SELECT id, name, created_at FROM dbo.orders WHERE status = ''Completed''',
    @filePath_name = 'D:\Export\orders_completed',
    @date = GETDATE();

生成文件：orders_completed_20240518.txt，UTF-16 LE 編碼，欄位以逗號分隔。

五、常見坑與對策

六、替代方案對比

• SSIS：視覺化、並行、錯誤處理豐富，適合複雜 ETL。
• PolyBase / OPENROWSET：直接寫入外部表（CSV/Parquet/ORC），無需外部進程。
• PowerShell + Invoke-Sqlcmd：跨平台、腳本化彈性大。

若需輕量、純 T-SQL、無額外元件依賴，bcp + xp_cmdshell 仍是最快落地的選擇。