Export data from SQL Server query to excel

Question

I want to export data from a query to excel file. I know that there is a lot of questions like these one here, but no one is acceptable in my situation.

For example, like this topic using OPENROWSET: T-SQL: Export to new Excel file

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=D:\testing.xls;', 
'SELECT * FROM [SheetName$]') select * from SQLServerTable

It only execute successfully when I create testing.xls myself, and also define exactly number of columns that will be export from my query in this excel file. Otherwise, an error occur: Column name or number of supplied values does not match table definition.

I also try another solution here: http://weblogs.sqlteam.com/mladenp/archive/2006/07/25/10771.aspx using Exec master..xp_cmdshell

It is really work, but, I heard that xp_cmdshell is a big security threat for SQL Server. So, may be I shouldn't use it.

Is there any other way I can try?

Solution

If you're looking for a quick and dirty solution, you can use Management Studio itself.

Here are the steps:

• write your query and run it
• right click the Results pane and select "Save Results As..."
• select your folder/filename and ensure CSV type is selected below
• now open your CSV file using Excel; if appropriate save it in Excel as a native Excel format

That won't help you if you need programmatic solution, in which case you have to use Microsoft (Microsoft.Office.Interop.Excel) or 3rd party solutions; you can even build one using Office Open XML.

If you need TSQL solution, OPENROWSET which you are mentioning in the question should be fine.

You can use Excel-only solution and import data using Data pane in the Excel itself (Import from SQL server).

Another possibility taht doesn't have to be coupled to SQL Server or Excel itself is Powershell. However, I'm not into Powershell so if you prefer this method you will have to investigate a bit more.

Also, this is a bit outdated but interesting read.

Comments

[user2500561]

I need a TSQL solution, with OPENROWSET, I need to create excel file myself, define columns for it, otherwise I got error: Column name or number of supplied values does not match table definition.