This post contains a guide on how to export data from a SQL Server database using SSMS.
SSMS, SQL Server Management Studio, is the standard application that most people use to run queries for a Microsoft SQL Server Database. The default export option in SSMS is set as Column-Aligned, and we need to change it to Comma-Delimited to get our query output formatted correctly for CSV.
To export a SQL query to CSV in SSMS we use the Results to File option which is shown in the demo below in this post. We use this instead of the standard query output option, Results to Table.
The first part of this post runs through how to export a SQL query to a file using the default output options in SSMS, and then we are changing the output format column options as mentioned above.
# Export SQL Server Query to File (Default SSMS Options)
# Change SSMS Output Format Options (for CSV Files)
Export SQL Server Query to File
To export a query the simplest way, would be for us to click Results to File as marked below.
When you run the query, it’ll prompt asking where and what to save the file as.
The default file type is as shown above, a .rpt file. If we open that in NotePad++, it’s looking like this:
We’ll save it as CSV this time.
You will now likely want to change the delimiter after looking at this output file. This .rpt file should not be opened as CSV.
Change SSMS Output Format Options
Now that we’ve looked at the default SSMS export file option, let’s change it to Comma Delimited so we can open SQL query outputs as CSV.
Within SSMS, go to Tools > Options as shown in the screenshot below.
In the Options window that appears, navigate to Query Results > SQL Server > Results to Text
The default option is shown above, Column aligned. Change this to Comma delimited.
We now need to disconnect our current session within the SSMS Object Explorer and connect back to the SQL Server instance.
Now, if we run that query as we did above outputting as text, the output file should be formatted correctly when saved as CSV.
You may have identified an issue with the exported CSV data, and it contains “rows affected” at the end of it. The reason for this is that we need to include SET NOCOUNT within the SQL query when using Results to Text. I talk more about this in another one of my posts – Count Rows in CSV Files > Checking for Data Issues
I hope this guide has helped you export a SQL query to CSV today. If you would like more SSMS-related tips, feel free to have a look at my SSMS page which is frequently updated.
0 Comments