In SQL Server, how do I save the results of a query to a txt file?

Question

In SQL Server, how do I export the results of a query to a txt file. I tried tools>options>results to file. But this appears to have saved the Messages the query generated instead of the Results the query generated.

Answer 1

Options:

• Use sqlcmd at the command line to execute your SQL statement; the results will arrive as text and can be trivially saved.
  • See https://stackoverflow.com/questions/425379/how-to-export-data-as-csv-format-from-sql-server-using-sqlcmd
• Use bcp at the command line to execute your SQL statement with the "queryout" parameter.
• Use SSMS to grid as you are, right-click the upper left cell of the results pane and use the Save Results As... option
• Use SSMS to file; this will of course save the file on the machine SSMS is running on. Tools->Options->Results to file is where you set options like delimiter, maximum size, and so on. Instead, you want Query->Results To->Results to File or Ctrl-Shift-F (by default)
• Other non-SQL ways like Powershell or any application language or MS Access or Excel with a DSN data source or SSIS or many many many other ways.

Answer 2

A manual way of achieving it is to CRTL+T on your SSMS to enable the option Results to Text. Execute your query and right click the Results > Save results as > Save as Type > All Files and name your file something.txt.

Answer 3

If you are not restricted only to SSMS, you can use Powershell

Invoke-Sqlcmd -ServerInstance MyInstance -Database MyDB -Query "SELECT * FROM Mytable" | Export-CSV "C:\MyResults.csv"