將SQLServer結果導出為excel文件

相信大家常常會遇到將SqlServer查詢結果導出到Excel的問題。如果導出的次數少，直接“Save Results As...”就是了；但是當要分別在每個表取樣，那就相當麻煩了。今天就為大家提供一個脫離office組件的可以將語句結果導出到Excel的過程，希望會對大家有幫助！

---導出到Excel
---使用說明：
-- 1.執行時所連接的服務器決定文件存放在哪個服務器
-- 2.遠程查詢語句中，要加上數據庫名
ALTER PROC ExportFile
@QuerySql VARCHAR(max)
,@Server VARCHAR(20)
,@User VARCHAR(20)
,@Password VARCHAR(20)
,@FilePath NVARCHAR(100) = 'c:ExportFile.xls'
AS
DECLARE @tmp VARCHAR(50) = '[##Table' + CONVERT(VARCHAR(36),NEWID())+']'
BEGIN TRY
DECLARE @Sql VARCHAR(max),@DataSource VARCHAR(max)='';
--判斷是否為遠程服務器
IF @Server <> '.' AND @Server <> '127.0.0.1'
SET @DataSource = 'OPENDATASOURCE(''SQLOLEDB'',''Data Source='+@Server+';User ID='+@User+';Password='+@Password+''').'
--將結果集導出到指定的數據庫
SET @Sql = REPLACE(@QuerySql,' from ',' into '+@tmp+ ' from ' + @DataSource)
PRINT @Sql
EXEC(@Sql)

DECLARE @Columns VARCHAR(max) = '',@Data NVARCHAR(max)=''
SELECT @Columns = @Columns + ',''' + name +''''--獲取列名（xp_cmdshell導出文件沒有列名）
,@Data = @Data + ',Convert(Nvarchar,[' + name +'])'--將結果集所在的字段更新為nvarchar（避免在列名和數據union的時候類型沖突）
FROM tempdb.sys.columns WHERE object_id = OBJECT_ID('tempdb..'+@tmp)
SELECT @Data = 'SELECT ' + SUBSTRING(@Data,2,LEN(@Data)) + ' FROM ' + @tmp
SELECT @Columns = 'Select ' + SUBSTRING(@Columns,2,LEN(@Columns))
--使用xp_cmdshell的bcp命令將數據導出
EXEC sp_configure 'xp_cmdshell',1
RECONFIGURE
DECLARE @cmd NVARCHAR(4000) = 'bcp "' + @Columns+' Union All ' + @Data+'" queryout ' + @FilePath + ' -c -T'
PRINT @cmd
exec sys.xp_cmdshell @cmd
EXEC sp_configure 'xp_cmdshell',0
RECONFIGURE
EXEC('DROP TABLE ' + @tmp)
END TRY
BEGIN CATCH
--處理異常
IF OBJECT_ID('tempdb..'+@tmp) IS NOT NULL
EXEC('DROP TABLE ' + @tmp)
EXEC sp_configure 'xp_cmdshell',0
RECONFIGURE

SELECT ERROR_MESSAGE()
END CATCH

PS：先不要著急使用，該版本是基于xp_cmdshell的，因為要創建文件，所以要保證你的用戶能有文件管理的權限，通常簡單點的方法就是將sql server的啟動用戶設置為本地系統用戶

生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈