39

MySQL had a nifty command SELECT ... INTO OUTFILE that could write the result set into a file (CSV format or some other optional format).

I am currently using SQL Server Management Studio to query an MS-SQL backend server. I have multiple SQL queries and would like to write the output result set into a file. Is there any way I could store the results from a query directly into a file?

Legend
  • 113,822
  • 119
  • 272
  • 400

2 Answers2

64

In SSMS, "Query" menu item... "Results to"... "Results to File"

Shortcut = CTRL+shift+F

You can set it globally too

"Tools"... "Options"... "Query Results"... "SQL Server".. "Default destination" drop down

Edit: after comment

In SSMS, "Query" menu item... "SQLCMD" mode

This allows you to run "command line" like actions.

A quick test in my SSMS 2008

:OUT c:\foo.txt
SELECT * FROM sys.objects

Edit, Sep 2012

:OUT c:\foo.txt
SET NOCOUNT ON;SELECT * FROM sys.objects
gbn
  • 422,506
  • 82
  • 585
  • 676
  • 1
    Thank You. I am actually looking to do this through the query directly. Because I have multiple queries, the result of the previous query is stacked when I am executing batch queries. – Legend Jun 15 '11 at 07:01
  • 1
    +1 Genius! That works like a charm. Just a last request: Is it possible to specify the delimiter to be a comma as opposed to the plain formatting it does? Some of my values contain spaces in them, so the default formatting will make my parser very complex. – Legend Jun 15 '11 at 07:17
  • @Legend: "Tools"..blah.. "results to text".."Custom delimiter" options in SSMS. Only affects new query windows (edited this comment after trying it) – gbn Jun 15 '11 at 07:23
  • Nice...but how to drop the "XX rows affected" at the end? Doesn't seem to be an option in results to text. – Andrew Mao Sep 19 '12 at 22:00
  • @AndrewMao: add a `SET NOCOUNT ON;` before the SELECT – gbn Sep 20 '12 at 06:45
  • 5
    (Typo in your edit, or you've come back in time from the far future to help people) – Damien_The_Unbeliever Sep 20 '12 at 06:49
  • Thanks for that. But one thing that really bugs me that I can't interleave multiple `:OUT` statements with queries in the same script. If I run the whole thing, everything gets dumped to the same file. Is this some stupid thing where it parses the `:OUT` statements first (mushing them all together) and then runs the queries? – Andrew Mao Sep 20 '12 at 21:13
  • 2
    @AndrewMao You need to use `GO` for each file. – mflodin Jan 15 '13 at 10:20
  • It seems like if your output exceeds a certain length it will only output to that length. This is happening to me. – Marnee KG7SIO Sep 30 '14 at 16:36
  • Good hints, since you have given shortcut to File mode, it is good to have shortcut to grid mode too which is CTRL+D and to Text mode is CTRL+T – Espanta Apr 22 '15 at 02:00
  • For more detail, SQLCMD is described [elsewhere on SE](http://stackoverflow.com/questions/9097109/in-sql-server-management-studio-what-is-sqlcmd-mode). –  May 14 '15 at 14:44
  • 1
    BAD ANSWER. Should warn user that SQL Server Management Studio is infamous for truncating columns at 256 characters; or at least in older versions it does. Don't attempt to use this method unless you are sure that all text fields in your query are short. – IAM_AL_X Jun 16 '17 at 00:43
  • 3
    @IAM_AL_X You can change that setting easily by just going to Query->Query Options->Results->Text and changing "Maximum number of characters displayed in each column" – Matthew Kruskamp Dec 06 '17 at 18:04
11

In SQL Management Studio you can:

  1. Right click on the result set grid, select 'Save Result As...' and save in.

  2. On a tool bar toggle 'Result to Text' button. This will prompt for file name on each query run.

If you need to automate it, use bcp tool.

Alex Aza
  • 76,499
  • 26
  • 155
  • 134
  • 1
    +1 Thank you for the bcp tool. When I try it out, it gives me an error `Copy direction must be either 'in', 'out' or 'format'`. Would you mind giving me a short example on how to use it using SQL queries from a file? – Legend Jun 15 '11 at 07:12
  • 1
    Example: `bcp "SELECT Name, GroupName FROM HumanResources.Department" queryout c:\department.txt -c –T` – Alex Aza Jun 15 '11 at 07:19
  • 1
    Do NOT use the SSMS method if you have any column longer than 256 characters. It will silently truncate the column, or at least in older version of SS it does. – IAM_AL_X Jun 16 '17 at 01:26
  • @IAM_AL_X You can change that setting easily by just going to Query->Query Options->Results->Text and changing "Maximum number of characters displayed in each column" – Matthew Kruskamp Dec 06 '17 at 18:04