TSQL - 将查询导出到xls / xslx / csv [英] TSQL - export query to xls /xslx / csv

问题描述

我在TSQL中有一个复杂的动态查询，我想导出到Excel。
[结果表包含文本超过255个字符的字段，如果重要]

我知道我可以使用Management Studio菜单导出结果，但我想通过代码自动执行。你知道吗？

提前感谢

解决方案

你可以看看 sp_send_dbmail 。这可以让您在查询运行后发送电子邮件，其中包含结果集的附加CSV。显然，这种方法的可行性将取决于你的结果集有多大。

链接文档的示例：

  EXEC msdb.dbo.sp_send_dbmail 
 @profile_name ='AdventureWorks2008R2管理员'，
 @recipients ='[e-mail]'，
 @query ='SELECT COUNT（*）FROM AdventureWorks2008R2.Production.WorkOrder 
 WHERE DueDate> '2006-04-30''
 AND DATEDIFF（dd，'2006-04-30''，DueDate）< 2'，
 @subject ='工单数量'，
 @attach_query_result_as_file = 1; 
  

I have a complicated dynamic query in TSQL that I want to export to Excel. [The result table contains fields with text longer than 255 chars, if it matters]

I know I can export result using the Management Studio menus but I want to do it automatically by code. Do you know how?

Thanks in advance.

解决方案

You could have a look at sp_send_dbmail. This allows you to send an email from your query after it's run, containing an attached CSV of the resultset. Obviously the viability of this method would be dependent on how big your resultset is.

Example from the linked document:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AdventureWorks2008R2 Administrator',
@recipients = '[e-mail]',
@query = 'SELECT COUNT(*) FROM AdventureWorks2008R2.Production.WorkOrder
              WHERE DueDate > ''2006-04-30''
              AND  DATEDIFF(dd, ''2006-04-30'', DueDate) < 2' ,
@subject = 'Work Order Count',
@attach_query_result_as_file = 1 ;

这篇关于TSQL - 将查询导出到xls / xslx / csv的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！