Is there any straightforward way to output text files (or CSV) from SQL Server?

Question

I've done a fair amount of tinkering and searching and the best option seems to be either:

1. sending output to text and copy/pasting into a text file or Excel OR

2. outputting to a somewhat unorthodox .rpt file, which I'm not sure what you'd do with--opening it in Excel doesn't preserve formatting present in the original output.

For what seems like a pretty common task, I'm surprised there isn't a simpler way to do this.

Can anyone suggest an easier way to go about this than the two methods I outlined?

Oh, and for what it's worth, I'm working on SQL Server 2008.

Answer 1

How about the BCP utility? http://msdn.microsoft.com/en-us/library/ms189569.aspx

Answer 2

Even through the SSMS GUI, it's still a relatively PITA process:

Plan A:

1. Tools, Options, Query Results, Results to Text <= Change output format from "fixed columns" to "delimit by tabs"

2. At that point, you can "Save results to File", and specify a .csv file

Plan B: fire up your favorite scripting language (like vb.net, for example) and just write a program that does the SQL query and writes the .csv. 10 lines, tops :)

Plan C: Yet another approach is to use some external program to do the query and convert the results for you. SQL Server comes with "BCP". You can easily write a .bat file to invoke it:

http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/

'Hope that helps

Answer 3

Are you using SQL Server Management Studio? If so, when you open a new query window, you can select to send output to a file. Query Menu -> Results To -> Results to File.

Answer 4

Chris, this is actually super-easy.

If your query results get displayed in the grid (as is the default), just right-click on the grid and choose Save Results As....