SQL Server 查詢結果匯出 Excel 完整指南：SSMS 精靈、BCP、OPENROWSET、CLR 与自動化存儲過程

將查詢結果匯出為 Excel 是 DBA、開發、業務分析師的高頻需求。本文對比五大主流方案：SSMS 另存為/精靈、BCP + CSV、OPENROWSET/OPENDATASOURCE、CLR 存儲過程、自動化存儲過程 (xp_cmdshell + BCP)，並提供生產級自動化腳本與權限配置指南。

方案對比速查

方案一：SSMS 匯入匯出精靈 (手動/一次性)

1. 資料庫右鍵 → 工作 → 匯出資料
2. 資料來源：SQL Server Native Client / .NET Framework Data Provider
3. 目的地：Microsoft Excel (選擇版本 .xls/.xlsx)
4. 指定資料表/檢視表或 撰寫查詢 (可用參數化)
5. 欄位對應、預覽、執行、可另存為 .dtsx 封裝供下次重用

方案二：BCP 高效匯出 CSV (自動化首選)

-- 匯出含標頭 CSV (UTF-8)
DECLARE @Cmd NVARCHAR(4000) = 
'bcp "SELECT ''Col1'',''Col2'',''Col3'' UNION ALL SELECT Col1,Col2,Col3 FROM Sales.Orders" queryout "C:\Export\Orders.csv" -S . -T -c -t, -r\n -C 65001';
EXEC xp_cmdshell @Cmd;  -- 需啟用 xp_cmdshell

Excel 直接開啟 CSV 即可，建議關聯 .csv 給 Excel。

方案三：OPENROWSET 直寫 .xlsx (Ad-hoc 靈活)

-- 需安裝 Microsoft Access Database Engine 2016/2019 (64-bit)
-- 需設定：EXEC sp_configure 'show advanced options',1; RECONFIGURE;
-- EXEC sp_configure 'Ad Hoc Distributed Queries',1; RECONFIGURE;

INSERT INTO OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;Database=C:\Export\Orders.xlsx;HDR=YES',
    'SELECT * FROM [Sheet1$]'
)
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Sales.Orders
WHERE OrderDate >= '2024-01-01';

坑點： 目標檔案與工作表必須預先存在；64 位 SQL 需 64 位 ACE；權限需 BULKADMIN 或 sysadmin。

方案四：生產級自動化存儲過程 (xp_cmdshell + BCP + 標頭)

適用：無法安裝 ACE、不想啟用 CLR、需排程產生可給業務開啟的 .xls/.csv

CREATE PROCEDURE dbo.usp_ExportToExcel
    @Query       NVARCHAR(MAX),      -- 完整 SELECT 查詢
    @FilePath    NVARCHAR(500),      -- 完整輸出路徑 (伺服器端)
    @Server      NVARCHAR(100) = '.',-- 目標伺服器，'.' 為本機
    @User        NVARCHAR(100) = NULL,
    @Password    NVARCHAR(100) = NULL,
    @WithHeader  BIT = 1
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @TmpTable NVARCHAR(100) = '##_Export_' + REPLACE(CONVERT(NVARCHAR(36), NEWID()), '-', '');
    DECLARE @Sql NVARCHAR(MAX), @Cols NVARCHAR(MAX), @Data NVARCHAR(MAX), @Cmd NVARCHAR(4000);
    
    -- 1. 建立暫存表承接結果 (支援遠端 OPENDATASOURCE)
    DECLARE @DataSource NVARCHAR(200) = '';
    IF @Server NOT IN ('.', 'localhost', '127.0.0.1', @@SERVERNAME)
        SET @DataSource = 'OPENDATASOURCE(''SQLOLEDB'', ''Data Source=' + @Server + ';User ID=' + @User + ';Password=' + @Password + ''').';
    
    SET @Sql = REPLACE(@Query, ' FROM ', ' INTO ' + @TmpTable + ' FROM ' + @DataSource);
    EXEC (@Sql);
    
    -- 2. 收集欄位名與轉換運算式
    SELECT @Cols = COALESCE(@Cols + ',', '') + '''' + name + '''',
           @Data = COALESCE(@Data + ',', '') + 'CONVERT(NVARCHAR(MAX), [' + name + '])'
    FROM tempdb.sys.columns WHERE object_id = OBJECT_ID('tempdb..' + @TmpTable);
    
    SET @Cols = 'SELECT ' + @Cols;
    SET @Data = 'SELECT ' + @Data + ' FROM ' + @TmpTable;
    
    -- 3. 啟用 xp_cmdshell (需 sysadmin)
    EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
    EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;
    
    -- 4. 執行 BCP：標頭 UNION ALL 資料
    SET @Cmd = 'bcp "' + @Cols + ' UNION ALL ' + @Data + '" queryout "' + @FilePath + '" -c -T -t, -r\n -C 65001';
    EXEC xp_cmdshell @Cmd, NO_OUTPUT;
    
    -- 5. 關閉 xp_cmdshell
    EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE;
    EXEC sp_configure 'show advanced options', 0; RECONFIGURE;
    
    -- 6. 清理
    EXEC ('DROP TABLE ' + @TmpTable);
END;
GO

-- 使用範例
EXEC dbo.usp_ExportToExcel 
    @Query = 'SELECT OrderID, CustomerID, OrderDate, TotalAmount FROM Sales.Orders WHERE OrderDate >= ''2024-01-01''',
    @FilePath = 'C:\Export\Orders_2024.csv';

權限與安全強化

• xp_cmdshell 僅限 sysadmin 執行。非 sysadmin 需建立 Proxy 帳號：EXEC sp_xp_cmdshell_proxy_account 'Domain\User', 'Password'; 並授權 GRANT EXEC ON xp_cmdshell TO [User];
• SQL Server 服務帳號需具備目標資料夾 寫入權限 (NTFS Modify)
• 生產環境建議關閉 xp_cmdshell，改用 Agent 作業步驟 CmdExec 類型呼叫 bcp.exe 或 PowerShell 腳本，權限隔離更佳

方案五：CLR 存儲過程 (真 .xlsx、豐富格式)

-- C# 專案 (Visual Studio SQL Server Database Project)
[SqlProcedure]
public static void ExportToExcel(string query, string filePath)
{
    using (var conn = new SqlConnection("context connection=true"))
    using (var cmd = new SqlCommand(query, conn))
    using (var reader = cmd.ExecuteReader())
    using (var pkg = new OfficeOpenXml.ExcelPackage()) // EPPlus
    {
        var ws = pkg.Workbook.Worksheets.Add("Data");
        ws.Cells.LoadFromDataReader(reader, true); // true = PrintHeaders
        pkg.SaveAs(new FileInfo(filePath));
    }
}
-- 部署：CREATE ASSEMBLY ... FROM '...dll' WITH PERMISSION_SET = EXTERNAL_ACCESS;
-- CREATE PROCEDURE usp_ClrExport @Query NVARCHAR(MAX), @FilePath NVARCHAR(500) AS EXTERNAL NAME AssemblyName.ClassName.ExportToExcel;

需啟用 clr enabled，組件簽署、權限集 EXTERNAL_ACCESS (需資料庫 TRUSTWORTHY ON 或非對稱金鑰登入)。

選型建議

• 手動/少量/真 .xlsx → SSMS 精靈
• 排程/大量/自動化/CSV 可接受 → BCP + Agent 作業
• Ad-hoc T-SQL 直寫 .xlsx、環境可控 → OPENROWSET (ACE)
• 複雜報表 (樣式、公式、多工作表) → CLR + EPPlus
• 受限環境、需 .xls 相容、純 T-SQL 自動化 → xp_cmdshell 存儲過程 (謹慎用)

掌握多種方案，才能在不同限制下交付最適解。