Export Data from SSMS to Excel

By:   |   Updated: 2022-07-07   |   Comments (1)   |   Related: > SQL Server Management Studio


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 gives us a few options to get our results to Excel. As with most problems, there is more than one way to solve it so we'll step through more than one solution that can all get us to the same place.

Getting Query Results from SSMS to Excel

These are the four methods of getting query results from SSMS to Excel we'll be covering:

  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

You would likely be working with a lot more data than this, but in the interest of keeping the screenshots clean and readable, and focusing on the solution instead of the data, we'll run the following T-SQL query in an SSMS Query Window to obtain 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

Note: Examples were done with SSMS 18.11.1 which is the latest version as of this writing and Microsoft Excel for Microsoft 365.

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 here is how to change it:

  1. Right-click in the Query Window
  2. Results To
  3. Results to Grid

Or simply Ctrl+D

Results to Grid

To make the setting permanent

  1. Tools
  2. Options…
SSMS Options
  1. Query Results
  2. SQL Server
  3. General
  4. Results to grids in the Default destination for results: dropdown
Output to Grid

Okay, now we have our results going where we want them, we can quickly and easily get those results into Excel with a simple copy and paste. Note: you can also select a contiguous subset of the records but for this example, we're presuming you've already filtered out what you want in the query.

  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 Ctrl+Shift+C
Copy Result Set

Open a blank workbook in Excel

  1. Right-click
  2. Paste

Alternatively, Ctrl+V

Paste Result Set

And your results are pasted in the workbook and ready for your analysis.

Pasted Result Set

One annoyance you may run into is you paste your data into Excel and concatenates your columns and pastes everything into one column like this.

Records Paste into Single Column 1

If this happens:

  1. Highlight the column
  2. Choose Data from the Ribbon
  3. ClickText to Columns
Records Paste into Single Column 2

And the Text to Columns Wizard will open.

  1. Next
Records Paste into Single Column 3
  1. Check the Tab box and be sure to uncheck any others
  2. Finish
Records Paste into Single Column 4

Now each column is in its own Excel column where it belongs.

Text Back to Columns

No need to save the change. Excel will remember it.

Save Results as a Delimited File

In addition to copying and pasting, SSMS also provides the ability to export the result set to either a comma delimited or tab delimited file that can be opened with Excel. Instead of highlighting and copying the results:

  1. Right-click in the Results window
  2. Save Results As…
Save Results As
  1. Select location to save the file
  2. Name file
  3. Select comma or tab delimited from Save as type dropdown
  4. Save
Output File
  1. Right-click file
  2. Open with and choose Excel
Open .csv

The file can now be saved as an Excel spreadsheet.

Saving Results Directly to a Fixed-Width .rpt File

Instead of a delimited file, we may want to work with a fixed-width file. SSMS also provides the functionality to output the results directly to a fixed-width file without going to the result pane.

  1. Right-click in Query Window
  2. Results To
  3. Results to File

Alternatively, Ctrl+Shift+F

Results To

Run the query and you'll be presented with a dialog box.

  1. Select the folder where you want to save the file
  2. Give the file a name
  3. Save
.rpt File

Open the file in Excel.

  1. Folder
  2. All files
  3. Click on file
  4. Open
.rpt File

This will open the Text Import Wizard.

  1. Fixed Width instead of the default of Delimited
  2. Uncheck My data has headers. If you don't have headers
  3. Next
Excel Text Import Wizard 1
  1. Verify / edit break line(s)
  2. Next
Excel Text Import Wizard 2
  1. Change data type if you wish
  2. Finish
Excel Text Import Wizard 3

Save as a .xlsx file.

Using the SQL Server Import and Export Wizard

We can also use the built-in SQL Server Import and Export Wizard. The Import and Export Wizard is a tool that uses SQL Server Integration Services (SSIS) to copy data from a source to a destination via an SSIS Package. Here, the source will be the query to obtain a list of product names and models and we'll export the results directly to an Excel file destination.

  1. Expand SQL Server in Object Explorer
  2. Right-click on the database you're exporting from
  3. Tasks
  4. Export Data…
Import and Export Wizard 1
  1. Next
Import and Export Wizard 2
  1. Select Data source: from the dropdown
  2. Confirm or change Server name:
  3. Leave Use Windows Authentication (if you're using the credentials you're running SSMS or choose Use SQL Server Authentication and enter login and password)
  4. Confirm or change Database:
  5. Next
Import and Export Wizard 3
  1. Choose a location for destination file
  2. Name file
  3. Open
Import and Export Wizard 4
  1. Choose a version from the Excel version dropdown
  2. Leave the First row has column names checked if your data has headers, uncheck if not
  3. Open
Import and Export Wizard 5
  1. Paste in SQL (take out GOs)
  2. Parse
Import and Export Wizard 6
  1. OK
Import and Export Wizard 7
  1. Next
Import and Export Wizard 8
  1. Next
Import and Export Wizard 9
  1. Next
Import and Export Wizard 10
  1. Verify actions
  2. Finish
Import and Export Wizard 11
  1. Check for Success
  2. Close
Import and Export Wizard 12

And here's your file.

Import and Export Wizard 13
Next Steps

Here are some links to more tips and tutorials on SSMS when exporting SQL Server data to Excel:






get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips


Article Last Updated: 2022-07-07

Comments For This Article




Thursday, July 7, 2022 - 5:34:50 PM - Rachael Hanna Back To Top (90242)
Kia ora Joe,
Another way to do this is to create a blank query in Power Query, then use this syntax:

let
Source = Sql.Database("MyServerName", "My_database_name", [Query="

PASTE the SQL Here

"])


in
Source

Close and load. You may get asked to authenticate the first time you run the query. The rerun just refresh from excel














get free sql tips
agree to terms