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.

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

If you are using username and password run following script

sqlcmd -i testquery.sql -S localhost -U pinaldave -P password -o TestResults.txt

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

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)