萬盛學電腦網

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

SQL Server 查詢結果導出 Excel 存儲過程:無 Office 組件、支援遠程、BCP 高效能

需將多張表抽樣導出 Excel?手工「Save Results As」太慢。本存儲過程使用 bcp + xp_cmdshell 直接導出含欄位名的 .xls 檔,無需安裝 Office,支援遠程伺服器查詢。

存儲過程完整碼

CREATE PROC ExportToExcel
    @QuerySql   NVARCHAR(MAX),      -- 查詢 SQL (含資料庫名)
    @Server     VARCHAR(20) = '.',  -- 目標伺服器,本機用 '.'
    @User       VARCHAR(20) = NULL, -- SQL 登入帳號
    @Password   VARCHAR(20) = NULL, -- 密碼
    @FilePath   NVARCHAR(200) = 'C:\Export\ExportFile.xls' -- 輸出路徑 (伺服器端)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @TmpTable NVARCHAR(50) = '##Tmp_' + REPLACE(CONVERT(VARCHAR(36), NEWID()), '-', '');
    DECLARE @Sql NVARCHAR(MAX), @DataSource NVARCHAR(MAX) = '';
    DECLARE @Columns NVARCHAR(MAX) = '', @Data NVARCHAR(MAX) = '';
    DECLARE @Cmd NVARCHAR(4000);

    BEGIN TRY
        -- 1. 遠程資料來源字串
        IF @Server NOT IN ('.', '127.0.0.1', '(local)')
            SET @DataSource = 'OPENDATASOURCE(''SQLOLEDB'',''Data Source=' + @Server + ';User ID=' + @User + ';Password=' + @Password + ''').';

        -- 2. 將查詢結果存入全域暫存表
        SET @Sql = REPLACE(@QuerySql, ' FROM ', ' INTO ' + @TmpTable + ' FROM ' + @DataSource);
        PRINT @Sql;
        EXEC (@Sql);

        -- 3. 組裝欄位名列 (BCP 無欄位名需自行 UNION)
        SELECT @Columns = @Columns + ',''' + name + '''',
               @Data    = @Data    + ',CONVERT(NVARCHAR(MAX),[' + name + '])'
        FROM tempdb.sys.columns 
        WHERE object_id = OBJECT_ID('tempdb..' + @TmpTable);

        SET @Columns = 'SELECT ' + STUFF(@Columns, 1, 1, '');
        SET @Data    = 'SELECT ' + STUFF(@Data, 1, 1, '') + ' FROM ' + @TmpTable;

        -- 4. 啟用 xp_cmdshell 並執行 BCP
        EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
        EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;

        SET @Cmd = 'bcp "' + @Columns + ' UNION ALL ' + @Data + '" queryout "' + @FilePath + '" -c -T -C 65001';
        PRINT @Cmd;
        EXEC master..xp_cmdshell @Cmd;

        -- 5. 關閉 xp_cmdshell、清理暫存表
        EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE;
        EXEC sp_configure 'show advanced options', 0; RECONFIGURE;
        EXEC ('DROP TABLE ' + @TmpTable);
    END TRY
    BEGIN CATCH
        IF OBJECT_ID('tempdb..' + @TmpTable) IS NOT NULL
            EXEC ('DROP TABLE ' + @TmpTable);
        EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE;
        EXEC sp_configure 'show advanced options', 0; RECONFIGURE;
        THROW; -- 重拋錯誤
    END CATCH
END

使用範例

-- 本機查詢
EXEC ExportToExcel 
    @QuerySql = 'SELECT TOP 100 * FROM AdventureWorks2019.Person.Person',
    @FilePath = 'C:\Export\Person.xls';

-- 遠程伺服器查詢
EXEC ExportToExcel
    @QuerySql = 'SELECT * FROM RemoteDB.dbo.Orders WHERE OrderDate >= ''2024-01-01''',
    @Server = '192.168.1.50',
    @User = 'report_user',
    @Password = 'P@ssw0rd',
    @FilePath = 'D:\Exports\Orders_2024.xls';

關鍵技術點

  • ##Tmp_ 全域暫存表避免並發衝突
  • OPENDATASOURCE 臨時連結遠程伺服器,無需建立 Linked Server
  • CONVERT(NVARCHAR(MAX), col) 統一類型避免 UNION 型別衝突
  • bcp -c -C 65001 字元模式 + UTF-8 編碼,Excel 開啟不亂碼
  • -T 信任連線 (Windows 驗證);SQL 驗證改 -U user -P pwd

權限需求

  • 執行者需 sysadmin 或 securityadmin (設定 xp_cmdshell)
  • SQL Server 服務帳號需具備輸出路徑寫入權限
copyright © 萬盛學電腦網 all rights reserved