TSQL - export query to xls /xslx / csv

问题:

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.

回答1:

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 ;

回答2:

One way is to use bcp which you can call from the command line - check out the examples in that reference, and in particular see the info on the -t argument which you can use to set the field terminator (for CSV). There's this linked reference on Specifying Field and Row Terminators.

Or, directly using TSQL you could use OPENROWSET as explained here by Pinal Dave.

Update: Re;: 2008 64Bit & OPENROWSET - I wasn't aware of that, quick dig throws up this on MSDN forums with a link given. Any help?

Aside from that, other options include writing an SSIS package or using SQL CLR to write an export procedure in .NET to call directly from SQL. Or, you could call bcp from TSQL via xp_cmdshell - you have to enable it though which will open up the possible "attack surface" of SQL Server. I suggest checking out this discussion.

回答3:

Some approaches here: SQL Server Excel Workbench

回答4:

I needed to accept a dynamic query and save the results to disk so I can download it through the web application.

insert into data source didn't work out for me because of continued effort in getting it to work.

Eventually I went with sending the query to powershell from SSMS Read my post here How do I create a document on the server by running an existing storedprocedure or the sql statement of that procedure on a R2008 sql server

Single quotes however was a problem and at first i didn't trim my query and write it on one line so it had line breaks in sql studio which actually matters.