Once you have a query that is returning the data you like in SSMS (SQL Server Management Studio), you may want to move the result set into a format that you can use in other tools. While copying and pasting the results works well for smaller sets of data, there are cases where the quantity of data returned does not fit into an Excel spreadsheet or you may want to import data into a different tool. In such cases, you need to export the data from the query set to a format that can be referenced by other tools.

Option 1: Execute Query Results to a File

To export query results to a file, follow these steps:

1. Build your query in SSMS.
2. Configure the output to file options by going to Tools > Options > Query Results > SQL Server > Results to Text. Set the output format to CSV and save the changes.
3. Press Ctrl+Shift+F to set the query to file destination (or select Query > Results To > Results to File).
4. Execute the query. You will be prompted to enter a file name and location for the output file. The file will be created with a .rpt extension, but it is just a plain text file.
5. After the export, you can change the extension of the output file to .CSV if you prefer, and then open the file to verify the contents.

Option 2: Export Query Results Using SQL Server Import and Export Wizard

If you prefer a more guided approach, you can use the SQL Server Import and Export Wizard to export query results to a CSV file:

1. Build your query in SSMS and copy it to the clipboard.
2. In the Object Explorer, right-click on the database you wish to export data from and select Tasks > Export Data.
3. The SQL Server Import and Export Wizard will open with the data source defaulted to the database you right-clicked on. Leave this as it is and click Next.
4. Select Flat File Destination for the destination and provide the file name and other options. Click Next to continue.
5. Select the Write a query to specify the data to transfer radio button and paste your query code into the SQL Statement box. Click Parse to ensure the syntax is correct, and then click OK to close the dialog box and Next to continue.
6. You can click Edit mappings if you need to map data types and transformations during the export for more accurate data. When ready, click Next.
7. Leave Run immediately marked and click Next.
8. Review the actions to be performed and click Finish.
9. Once the process is complete, you will see a success message and a count of records that were transferred. Click Close when done.

Now you can use the exported CSV file in whichever tool you prefer.

For more detailed instructions and screenshots, you can read the full article here.