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.
- Select short cut CTRL + SHIFT + F
- Go to Tool Bar >> Query >> Results To >> Results to File
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.
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.
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.
That’s it. We are done!
Reference: Pinal Dave (https://blog.sqlauthority.com)
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
I agree with Hans Dahners as we have better way to fetch data directly from within Excel from SQL DB.
Thanks.
Regards,