Problem: You’re running an ad-hoc query in a Microsoft SQL Server database with SQL Server Management Studio (SSMS) and need to further analyze the result set in an Excel spreadsheet. How do you export the data?

Solution

SSMS provides several options to export query results to Excel. In this article, we will explore four different methods:

1. Copy and paste from the results tab
2. Save results as a delimited file
3. Saving results directly to a fixed-width .rpt file
4. Using the SQL Server Import and Export Wizard

Let’s assume we have a T-SQL query that retrieves a list of product names and models from the AdventureWorksLT2019 database:

USE AdventureWorksLT2019;
GO

SELECT [p].[Name]  AS [ProductName],
      [pm].[Name] AS [ProductModel]
FROM   [SalesLT].[Product]      [p]
  JOIN [SalesLT].[ProductModel] [pm] ON [p].[ProductModelID] = [pm].[ProductModelID]
ORDER BY [p].[Name];
GO

Method 1: Copy and Paste from Results Tab

This method requires the results to be sent to the default grid output. If the results are not going to the grid, you can change the setting by right-clicking in the Query Window and selecting “Results To” > “Results to Grid” or simply pressing Ctrl+D.

To copy and paste the results into Excel:

1. Click the box in the upper left-hand corner of the Results pane to highlight all records
2. Click on “Copy with Headers” or press Ctrl+Shift+C
3. Open a blank workbook in Excel
4. Right-click and select “Paste” or press Ctrl+V

If the columns are concatenated when pasted into Excel, you can use the “Text to Columns” feature to separate them:

1. Highlight the column
2. Click “Data” from the Ribbon
3. Click “Text to Columns”
4. In the Text to Columns Wizard, check the “Tab” box and uncheck any others
5. Click “Finish”

Method 2: Save Results as a Delimited File

In addition to copying and pasting, SSMS allows you to export the result set to a comma delimited or tab delimited file that can be opened with Excel.

1. Right-click in the Results window
2. Select “Save Results As…”
3. Choose a location to save the file
4. Name the file
5. Select either comma or tab delimited from the “Save as type” dropdown
6. Click “Save”
7. Right-click the saved file and select “Open with” > “Excel”

Method 3: Saving Results Directly to a Fixed-Width .rpt File

If you prefer to work with a fixed-width file instead of a delimited file, SSMS provides the functionality to output the results directly to a fixed-width file.

1. Right-click in the Query Window
2. Select “Results To” > “Results to File” or press Ctrl+Shift+F
3. Run the query and a dialog box will appear
4. Select the folder where you want to save the file
5. Give the file a name
6. Click “Save”
7. Open the saved file in Excel by clicking “File” > “Open” > “Browse”
8. In the “Open” dialog, select “All files” from the “Files of type” dropdown
9. Click on the saved file and click “Open”
10. The Text Import Wizard will open
11. Select “Fixed Width” instead of the default “Delimited”
12. Uncheck “My data has headers” if your data does not have headers
13. Click “Next” and verify/edit the break line(s)
14. Click “Next” and change the data type if needed
15. Click “Finish”
16. Save the file as a .xlsx file

Method 4: Using the SQL Server Import and Export Wizard

The SQL Server Import and Export Wizard, powered by SQL Server Integration Services (SSIS), allows you to copy data from a source to a destination. In this case, we will use the wizard to export the query results directly to an Excel file.

1. Expand “SQL Server” in Object Explorer
2. Right-click on the database you’re exporting from
3. Select “Tasks” > “Export Data…”
4. In the wizard, select the data source from the dropdown
5. Confirm or change the server name
6. Choose the authentication method
7. Confirm or change the database
8. Choose a location for the destination file
9. Name the file
10. Choose the Excel version
11. Check or uncheck the “First row has column names” option
12. Click “Next” and paste the SQL query (remove “GO” statements)
13. Click “Parse” and verify the columns
14. Click “Next” and review the actions
15. Click “Finish” to start the export
16. Check for success and close the wizard

With these methods, you can easily export SQL Server query results to Excel for further analysis and reporting.

Article Last Updated: 2022-07-07