SQL SERVER – Copy Data From SSMS Query Result to Excel

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.

SQL SERVER - Copy Data From SSMS Query Result to Excel export1

SQL SERVER - Copy Data From SSMS Query Result to Excel export2

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.

SQL SERVER - Copy Data From SSMS Query Result to Excel export3

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:

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – Cannot Shrink Log File 2 (SQLAuthorityDB_log) Because the Logical Log File Located at the End of the File is in Use
Next Post
SQL SERVER – Export Data From SSMS Query to Excel

Related Posts

6 Comments. Leave new

  • Hi Pinal,
    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?

    Reply
    • Hi Tom,

      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,
      Srini

      Reply
      • @Srini
        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.

        Tom

  • 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.

    Reply
  • Hi Pinal,

    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..

    Thanks,
    Srinivas

    Reply
  • Shantilal Suthar
    January 11, 2019 12:34 pm

    Hi Readers,

    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 :-)

    Reply

Leave a Reply

Menu