Just another day I received a question via email about how to Copy Data From SSMS Query Result to Excel? This is indeed a very simple question but trusts me not everyone knows everything and this blog is dedicated to learning new things – a complicated one or even a simple one.
Let us see how we can copy data from SSMS Query Results to Excel.
First of all – run the query in SSMS query editor and go to the results tab.
In the results tab, RIGHT CLICK on the result tab and select the option of save as type CSV.
Once you save the file with the extension CSV, you can open the same file with Excel and later on save that as an Excel file if you prefer.
There are many other ways to directly load the data from SQL query to Excel but I personally prefer this method as it is very easy.
Here are few other recent blog posts which you may find useful:
- SQL SERVER – Cleanup Plan Cache For a Single Database
- SQL Server Performance Tuning Practical Workshop – Relaunched
- When to Use sort_in_tempdb for Rebuilding Indexes? – Interview Question of the Week #207
Reference: Pinal Dave (https://blog.sqlauthority.com)
Is there an option somewhere to include the column headers, when one uses the method you outlined (Save Results As —> CSV file)? When I try this using SSMS v17.9, I get the data fine but no column headers. I have usually just selected the option to right-click and Copy with Headers.
Second question: Excel is a bit too helpful as it converts text values 1E001 and 1E002 to numbers in scientific notation as 1.00E+1 (numeric value 10) and 1.00E+2 (numeric value 100). For my data, these are definitely text values (they are actually airplane identifiers). Do you have any easy tricks up your sleeve to help prevent this conversion?
To display the column headers while saving, please navigate to Tools -> Options -> Query Results -> SQL Server -> Results to Grid.
On Results to Grid screen, click on check box “Include column headers when copying or saving the results” and hit ok button. After this, any queries which are executed in new sessions will have header info available. Please note that the existing sessions won’t reflect the changes made (means header info not copied for already existing sessions).
Thanks. That works, however, the *other* right-click option “Copy” now acts the same as “Copy with Headers”. It is interesting to note that Copy or Copy with Headers seems to produce tab-delimited results whereas the Save Results As option seems to produce comma-delimited results.
You can also copy the query from SSMS and paste in the new query window in Excel 2016. The result set is much better and in the table output in Excel which is easy to analyze.
Instead of results to grid, we can switch to “Results to file” and redirect the generated output directly to a file instead of manually right clicking on the results tab and saving the results to a file..
I am looking for vise-versa things i.e. an easy way to export data from excel to database.
I know how to do this but what I do is very long and complex thing.
Let me know if you know an easy way to do this.
Thanks and have a good day :-)