Sql Server 数据库表查询结果导出为excel文件

网络整理 - 08-20

  相信大家常常会遇到将SqlServer查询结果导出到Excel的问题。如果导出的次数少,直接“Save Results As...”就是了;

  1.1准备好查询语句

  1.2选择数据库,启动导入和导出向导

Sql Server 数据库表查询结果导出为excel文件

  1.3选择数据源

Sql Server 数据库表查询结果导出为excel文件

  1.4选择目标

Sql Server 数据库表查询结果导出为excel文件

  1.5

Sql Server 数据库表查询结果导出为excel文件

  1.6

Sql Server 数据库表查询结果导出为excel文件

  1.7

Sql Server 数据库表查询结果导出为excel文件

  1.8

Sql Server 数据库表查询结果导出为excel文件

  后续步骤不再附图,一直点“下一步”按钮就好。

  2、但是当要分别在每个表取样,那就相当麻烦了。今天就为大家提供一个脱离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;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

  先不要着急使用,该版本是基于xp_cmdshell的,因为要创建文件,所以要保证你的用户能有文件管理的权限,通常简单点的方法就是将sql server的启动用户设置为本地系统用户

Sql Server 数据库表查询结果导出为excel文件

Sql Server 数据库表查询结果导出为excel文件

  好了,现在我们来执行看看:

  --查询分析器连接哪个服务器,文件就在哪个服务器上

  --本地导出

  EXEC dbo.ExportFile @QuerySql = 'select * from sys.objects', -- varchar(max)

  @Server = '.', -- varchar(20)

  @FilePath = N'c:objects.xls' -- nvarchar(100)

  --远程导出

  EXEC dbo.ExportFile @QuerySql = 'select * from master.sys.objects', -- varchar(max)

  @Server = '192.168.1.52', -- varchar(20)

  @User = 'sa', -- varchar(20)

  @Password = 'sa', -- varchar(20)

  @FilePath = N'c:52objects.xls' -- nvarchar(100)

  执行结果如下,显示导出条数,就没有报错,再看看你的C盘,多了2个文件就大功告成了:

Sql Server 数据库表查询结果导出为excel文件

Sql Server 数据库表查询结果导出为excel文件