From SQL Server, how can I export to Excel with the column headers?

Question

I have a query that has approx 20 columns and I would like to export this to an Excel file with the column headers. I thought this would be easy to figure out but no luck! I searched the web and found one suggestion that did not end up working so I am stuck.

Anyone have any working ideas?

Thanks,

Answer 1

That is the default if you use the Import/Export wizard. You can also save this out to an SSIS package if you want to customize the export.

Answer 2

Maybe this will get you started:

http://weblogs.sqlteam.com/brettk/archive/2005/04/13/4395.aspx

EDIT:

The example uses BCP, and to get it to work you may have to reconfigure some features. I ran the following simpler example (which will export data without headers) to better understand what BCP does:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

-- Dump the table
DECLARE @sql VARCHAR(8000)
SELECT @sql = 'bcp <database>.dbo.<table> out d:\bcp\output.txt -c -t, -T -Slocalhost'
EXEC xp_cmdshell @sql

Answer 3

I typically do this by simply click the upper left corner in the results grid, copy, and then paste into Excel. There is one catch, you need to go into options->query results-> SQL Server->results to grid (or text if you want to save to file for import into excel) and turn on include column headers when copying or saving the results. I find this works great.