SQL SERVER – Automatically Store Results of Query to File with sqlcmd

I receive lots of email everyday and I do answer almost every email I receive. However, I prefer to answer all the technical questions on my facebook page. With the help of social media it is very easy to reach out to multiple people with the same problem. Here is the recent question which I received on my facebook page.

“How I automatically execute my T-SQL script and save output of the query (resultset) in a different file automatically? I do not want to use SSMS”

Well, very interesting and simple question. It is very easy to direct the output of the query to a new file with the help of sqlcmd.

Let us work on this together.

Step 1: Create SQL file which you want to execute. I have created SQL file testquery.sql and saved it in my drive.

SQL SERVER - Automatically Store Results of Query to File with sqlcmd sqlcmdoutput1

Step 2: Execute following sqlcmd command on prompt:

sqlcmd -i SQLFile.sql -S ServerLocation -E -o OutFileName.txt

If you are using windows authentication execute following script

sqlcmd -i testquery.sql -S localhost -E -o TestResults.txt
SQL SERVER - Automatically Store Results of Query to File with sqlcmd sqlcmdoutput2 

If you are using username and password run following script

sqlcmd -i testquery.sql -S localhost -U pinaldave -P password -o TestResults.txt
SQL SERVER - Automatically Store Results of Query to File with sqlcmd sqlcmdoutput3 

Step 3: Open the file generated by sqlcmd in the drive

SQL SERVER - Automatically Store Results of Query to File with sqlcmd sqlcmdoutput4

This is indeed a very simple process. If you have any question, you can go to my Facebook Page and ask question.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – Simple Puzzle with UNION – Part 5
Next Post
SQL – Weekend Project – Watching Technically Seth Series – What is your plan for this weekend?

Related Posts

21 Comments. Leave new

  • You can rename the file in a seperate step to get a unique name by adding a date in a roundabout way using a windows variable, eg:
    REM SQLCMD set filename variable name
    REM -U user, P password, Q quit after query, nocount on, -o output file -h-1 remove column headers, -W remove trailing spaces
    sqlcmd -S server -U user -P pass -Q”SET NOCOUNT ON; SELECT REPLACE( CONVERT(VARCHAR,MAX(StartDate),103) + ‘_’ + CONVERT(VARCHAR,MAX(EndDate),103),’/’,” ) FROM database.dbo.DateRecord” -oDateVar.txt -h-1 -W

    REM create environment variable PGvar
    set /p DateVar= < DateVar.txt

    REM rename files based on variable
    rename OriginalOutput.txt OriginalOutput_%DateVar%.txt

    Reply
  • It answered my question !!!

    Reply
  • That answered my question !

    Reply
  • what does -E and -S stand in the above command???

    Reply
  • I have executed many query but in my output file I only get one line, whereas when I run the sql scripts manually from query analyser it shows (1 rows affected) multiple times down the results window, how can i get these multiple rows of output to show in my output text file using SQLCMD..?

    Reply
  • how can export the table data.

    Reply
  • can we do this using t sql?

    Reply
  • Using utilities we have lot of options like SQLCMD, BCP, OSQL, but how can we do this using TSQL?

    Reply
  • how do we get rid of the extra blank lines between each row in the result set?

    Reply
  • Good day

    I tried the above but my output file is not created. Please help

    Reply
  • Keneilwe Masake
    July 11, 2015 4:39 pm

    when i execute the sql query the results are displayed on the results pane but when i run the command to automatically save the results in text file, output file is created successfully, when i open it it shows there are some errors in the script though i ran the script and it displayed the results. Why?

    Reply
  • Works great. But is there a way to only output the results without the dashes and
    (1 rows affected)?

    Reply
  • How i remove the hyphen (—————–) which are in the second line of the CSV file? Many thanks

    Reply
  • my first row is blank, and I have 4 rows of dashes. I would like to know if I can eliminate these, as well as the “(X row(s) affected)” at the bottom

    Reply
  • Ganesh Sanap
    July 18, 2016 3:12 pm

    What if I want to store my query result into text file using SQL management studio script

    Without that CLT+SHIFT+F . With script like in Mysql INTO OUTFILE

    Reply
  • Steven Balderrama
    February 13, 2017 8:48 pm

    Where do you save the sql file to be run, C drive, D drive etc.?

    Reply
    • If you just give file name then it should be in the directory in which you are. Else you can also give complete path like sqlcmd -i”D:\ThisFolder\Filename.txt”

      Reply
  • How to save output in excel using cmd

    Reply
  • How can I add the date to the output file? ex: C:\Temp\OutFile20210615.txt

    Reply

Leave a Reply

Menu