Export SQL query data to Excel
I don't know if this is what you're looking for, but you can export the results to Excel like this:
In the results pane, click the top-left cell to highlight all the records, and then right-click the top-left cell and click "Save Results As". One of the export options is CSV.
You might give this a shot too:
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\Test.xls;','SELECT productid, price FROM dbo.product')Lastly, you can look into using SSIS (replaced DTS) for data exports. Here is a link to a tutorial:
http://www.accelebrate.com/sql_training/ssis_2008_tutorial.htm
== Update #1 ==
To save the result as CSV file with column headers, one can follow the steps shown below:
- Go to Tools->Options
- Query Results->SQL Server->Results to Grid
- Check “Include column headers when copying or saving results”
- Click OK.
- Note that the new settings won’t affect any existing Query tabs — you’ll need to open new ones and/or restart SSMS.
If you're just needing to export to excel, you can use the export data wizard. Right click the database, Tasks->Export data.
I had a similar problem but with a twist - the solutions listed above worked when the resultset was from one query but in my situation, I had multiple individual select queries for which I needed results to be exported to Excel. Below is just an example to illustrate although I could do a name in clause...
select a,b from Table_A where name = 'x'select a,b from Table_A where name = 'y'select a,b from Table_A where name = 'z'The wizard was letting me export the result from one query to excel but not all results from different queries in this case.
When I researched, I found that we could disable the results to grid and enable results to Text. So, press Ctrl + T, then execute all the statements. This should show the results as a text file in the output window. You can manipulate the text into a tab delimited format for you to import into Excel.
You could also press Ctrl + Shift + F to export the results to a file - it exports as a .rpt file that can be opened using a text editor and manipulated for excel import.
Hope this helps any others having a similar issue.