Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How to Copy Query Results From SQL Server 2005/2008 into Excel

I often work with DBAs who need to copy the results of a T-SQL query from SQL Server Management Studio (SSMS) into some portable format that is easy to save and comprehend. The easiest way that I have found is to right-click on the top left grid cell and choose either “Copy” or “Copy with Headers” (depending on whether you are running the 2005 or 2008 version of SSMS).  Only SQL Server 2008 SSMS has the “Copy with Headers” choice.

Once you make your choice, all of the cells in the result grid will be copied to the Windows Clipboard. Then, you can paste (Cntrl-V) the results into a tab of an Excel spreadsheet.

image

If you are using SQL Server 2005 SSMS, you need to enable one option to get the column headers to be copied with the other results. Just go to Tools, Options, then open Query Results, SQL Server, Results to Grid in the left tree-view control (see below). Then check the “Include column headers when copying or saving the results” option and click OK.

image

This is a far better solution than saving your results to a .rpt or .csv file, especially for the person who needs to read it later!

Comments

Posted by Pavel Pawlowski on 18 December 2009

Some time there is a problem, that data which you paste to excel are wrong formatted or the columns are not splited for headers or data.

It such scenarios is then easier to use Data->ImportExternalDate->New Database query and you can have exact data in excel.

If the result in grid is a result of some sequential processing and more commands, then you can simply output the data into the global temporary table and then make in excel query against this global temporary table to load the data.

Posted by Kendal Van Dyke on 18 December 2009

When using SSMS 2005 (and 2008, for that matter) you can change the options for the current query window only without having to change it as the default for all queries. Just right click on whitespace anywhere in the query window and choose "Query Options".

Posted by dennis.oconnor on 21 December 2009

Generally, when I have to provide an Excel file, I create a csv with headers. Then open with Excel, clean up by replacing NULLS with ' ', format any date fields, and then autofit columns. Finish up by saving as .xls. This generally gives the recipient a file they can use without having to do much if any formatting.

Posted by Glenn Berry on 21 December 2009

Good tips Kendal, Pavel, and Dennis!

Leave a Comment

Please register or log in to leave a comment.