在sql server中创建excel并从sql查询导出数据，而无需使用向导。 [英] Create excel and export data from sql query in sql server without using wizard.

问题描述

Hello Friend，



如果该路径上不存在文件并从sql查询导出数据，我必须在预定义文件夹路径上创建excel文件。但我不想为sql Wizard使用相同的。我想为它创建SQL存储过程，我将在Job中调用该存储过程。



但我想创建excel文件并仅通过商店程序将数据导出到它。



谢谢提前。

Hello Friend,

I have to created excel file on pre define folder path if file not exist on that path and export data from sql query. but I don't want to use for sql Wizard for same. I want create SQL store procedure for same and I will call that store procedure in Job.

But I want to create excel file and export data into it by store procedure only.

Thanks in advance.

推荐答案

使用下面的脚本

Use below script

DECLARE @PATH VARCHAR(500)
DECLARE @Name Varchar(500)
DECLARE @SQL VARCHAR(500)

SET @Name = TableData.xls'

SET @PATH = '\\C:\'

SET @SQL = 'SELECT * FROM TableName'

DECLARE @cmd VARCHAR(800)
SET @Cmd = 'mkdir ' +@PATH
EXEC master..xp_cmdshell @cmd

DECLARE @FILENAME VARCHAR(200)
SET @FILENAME = @PATH + @Name

EXEC sp_makewebtask
  @outputfile = @FILENAME,
  @query = @SQL,
  @nrowsperpage = 40000,
  @lastupdated=0 ,
  @resultstitle = '  '

这篇关于在sql server中创建excel并从sql查询导出数据，而无需使用向导。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！