SQL SERVER – Export Data From SSMS Query to Excel

Today’s blog post is actually a follow up of my earlier blog post SQL SERVER – Copy Data From SSMS Query Result to Excel. In the earlier blog post, we had discussed how to copy data from the resultset. In this blog post, we will discuss how we can directly export data from SSMS Query to Excel.

In SQL Server Management Studio (SSMS), open Query Window.

Next, select the option of sending Results to File. You can either do this by following ways.

  1. Select short cut CTRL + SHIFT + F
  2. Go to Tool Bar >> Query >> Results To >> Results to File

SQL SERVER - Export Data From SSMS Query to Excel queryexcel1

Once you run the query it will bring up the following pop up where we can save the result in the format of .rpt. You can save the file with any name. in the example, I have saved it with the name results.rpt.

SQL SERVER - Export Data From SSMS Query to Excel queryexcel2

Once you save the file with you can now open excel first.

Go to Menu >> Files >> Open and locate this file.

The file will bring up the following menu, where you can just review the data and click on the next.

SQL SERVER - Export Data From SSMS Query to Excel queryexcel3

SQL SERVER - Export Data From SSMS Query to Excel queryexcel4

SQL SERVER - Export Data From SSMS Query to Excel queryexcel5

You will see that excel will have all the data in the separate columns. You can save this file with your preferred format or keep it as xls file.

SQL SERVER - Export Data From SSMS Query to Excel queryexcel6

That’s it. We are done!

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

Quest

Excel, SQL Server, SQL Server Management Studio, SSMS
Previous Post
SQL SERVER – Copy Data From SSMS Query Result to Excel
Next Post
SQL SERVER – Identify Datatype Using SQL_VARIANT_PROPERTY Function

Related Posts

2 Comments. Leave new

  • Dear Pinal,

    First, I wish to thank you for your continuing dedication to helping and educating people around the world in matters relating to SQL.

    Next, I wish to leave a comment as I believe there is a better way to achieve the goal of transferring data from a database into Excel than by using the means that you describe in this post.

    Personally, I find that it is easier to fetch the data directly from within Excel than having to manually split the data into columns; the link below explains how to do this using Microsoft Query Editor:
    https://support.office.com/en-us/article/use-microsoft-query-to-retrieve-external-data-42a2ea18-44d9-40b3-9c38-4c62f252da2e
    As an added advantage, the data type of each column is automatically fetched and applied. So, this means that it is unlikely that Excel will try to interpret the data in any other way than how it was intended / declared in the source table.

    Kind regards,
    Hans

    Reply
  • Shantilal Suthar
    January 17, 2019 12:31 pm

    I agree with Hans Dahners as we have better way to fetch data directly from within Excel from SQL DB.

    Thanks.

    Regards,

    Reply

Leave a Reply