Export multiple sql query results 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

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)

Related Posts

• Remove From My Forums

• Question

• Hi,

  I have many queries within on SQL script. I would like to automatically export all these query result to an Excel file, so that that each query would have its own Heading. Is this possible to do?

  Thanks in advance!

  • Moved by Wednesday, December 15, 2010 3:02 PM Possibly better answer from TSQL forum (From:SQL Server Database Engine)

Answers

• Hi Kalman,

  I think that if we want to export different query result to a sheet with its own heading, we need to insert a header text at fist as follows:

  USE AdventureWorks
  GO
  --insert data into a 2007-2010 Excel file 
  
  
  --Insert a header and results
  INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0;Database=E:\Test\TestExcel.xlsx;',
  'SELECT * FROM [Employee$]')
  SELECT 'HumanResources.Employee' AS HeaderText
  GO
  
  INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0;Database=E:\Test\TestExcel.xlsx;',
  'SELECT * FROM [Employee$]')
  SELECT TOP 10 EmployeeID FROM HumanResources.Employee
  GO
  
  --Insert another header and results
  INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0;Database=E:\Test\TestExcel.xlsx;',
  'SELECT * FROM [Employee$]')
  SELECT 'umanResources.Department' AS HeaderText
  GO
  
  INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0;Database=E:\Test\TestExcel.xlsx;',
  'SELECT * FROM [Employee$]')
  SELECT TOP 10 DepartmentID FROM HumanResources.Department
  GO
  
  
  

  
  Thanks,
  Ai-Hua Qiu

  ---

  Ai-hua Qiu[MSFT]
  MSDN Community Support | Feedback to us
  Get or Request Code Sample from Microsoft
  Please remember to mark the replies as answers if they help and unmark them if they provide no help.

  • Marked as answer by Kalman Toth Wednesday, January 12, 2011 11:13 PM

How do I export multiple query results from SQL Server to Excel?

How do I export SQL query results to Excel automatically?

How do I export a large amount of data from SQL to Excel?

How can I get multiple query results in SQL?