Export data from table to excel column name

Question

How can I export data from table to excel with column name.

Answer 1

In SQL Server 2005, you can use SQL Server Integration Services (SSIS) to export data to Excel. Here's an article that might get you started:

http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1306800,00.html

On 2000, you can use Data Transformation Services (DTS). Using DTS to export data to Excel was much easier than doing it in SSIS, it's too bad they made it more complicated in SSIS.

http://support.microsoft.com/kb/319951

There is also a simpler way to do it using OPENROWSET (this may require creating the Excel file before doing the export):

http://www.mssqltips.com/tip.asp?tip=1202

One additional simple way to do this is to just query your database by dumping the values into a comma-separated list, then saving off the results as a .csv file.

The simpler methods might require some clever coding to get the column names into the excel spreadsheet.

Answer 2

You have a few options.

1. In SSMS, Tools > Options > Query Results > SQL Server > Results To Grid > Include Column Headers when Copying or Saving the Results. You can then manually copy and save the grid results.
2. Use Microsoft Query from within Excel.
3. Right-click on the database, select Tasks > Export Data. Use the Import/Export wizard.
4. Use SSIS
5. Programatically extract the values.

There are probably more, but that's a pretty good selection!