SQL Server Management Studio (SSMS) – how to save results with headers

Many people want to find out how to use ‘SSMS save results with headers’ functions to save the results of a query to an Excel file.

Scenario

You wish to save the results of a query to an Excel file and in doing so you also want to capture column headers. Believe it or not, this is an easy task yet there are so many folks that don’t know how to do it properly.

Solutions

One way that I’ve seen most people use to save results to the grid in SSMS is to choose “Save Results As…” option and then save to CSV file which can be opened in Excel:

SSMS save results with headers using Save Results As option from the results grid

Opening this newly created CSV file will look nothing like how it does in SSMS even if you play with the import settings a lot. On top of that, you are missing column headers. However, this can be easily fixed by telling SSMS to include column names by default when copying or saving the results. Here’s how to do it:

  1. Go to Tools > Options
  2. Navigate to Query Results > SQL Server > Results to Grid, then check “Include column headers when copying or saving the results” option:

    Option for displaying SSMS save results with headers

  3. Click OK to save changes, close and restart SSMS to apply changes
  4. If you use the ‘Save Results As…’ option now in the Results tab and open the saved excel file, you will be able to see all column headers as well

Now, if we execute another query, a simpler solution is to just select the whole results grid by clicking the area marked on the screenshot below:

Option to select all grid cells from SSMS results grid

This action will select all grid cells and now we can right-click anywhere within the results grid, use the right-click and from the context menu click either ”Copy” or “Copy with Headers” option to place the data into the clipboard:

SSMS save results with headers right-click context menu options from the results grid

Now we just need to run Excel, open a blank workbook, if needed the cell start position can be changed, but let’s just leave it at A1 and use Ctrl + V from the keyboard to paste the data in Excel. Here’s how it looks:

Excel document with column headers when copying or saving the results

Not bad, right? There are more ways to save SSMS results with headers data like the SQL Server Import and Export Wizard but this includes many steps that are just time-consuming and I don’t even want to go through all of them because there is a much quicker solution.

ApexSQL Complete add-in for SSMS and Visual Studio has a neat feature called Export to Excel. This feature is designed to easily export data from the results grid directly to Excel. Once installed, the add-in will add an extra option to the right-click context menu within the results grid called “Export to Excel” like shown below in the figure. To export data to an Excel file, simply select desired data from the result grid, and from the context menu click the Export to Excel option:

SSMS save results with headers option from ApexSQL Complete right-click context menu

This action will launch Excel, and the exported data will look like this:

Excel document with column headers when exporting the results using Export to Excel feature from ApexSQL Complete

As can be seen, the column headers are included and formatted nicely. The same goes for the rest of the data.

Conclusion

We learned how we can save results with headers in SSMS in this short article. We also covered the process to export data directly to an Excel file from the Results grid with just a few clicks using the ApexSQL feature.

 

August 13, 2019