Batch File To Print Result of SQL to Text File

17 January 17 Ben Dickman

It is a very easy task to write the results of a SQL query to a text file with a simple batch file. Batch files can be very powerful and this is the ideal place to start, with a basic example to play with.

The first step to achieve this is to save your SQL you would like to run. Something like LogEmployees.sql. The following example is a very straight forward query to show the concept.

select name
from Employee
order by name

Now create a text file where the results of the SQL query will be logged to, something like EmployeeDetails.txt. We then need to create a batch file that will execute the SQL and log the results to a text file. Copy the following, remembering to change the Server name, SQL file location and Text file location. Save the file as something like LogEmployeesDetails.bat

echo off
sqlcmd -E -S your_server_name\database_name -i C:\LogEmployees.sql >> C:\EmployeeDetails.txt

The sqlcmd is a way to execute SQL statements (like you could not guess that!). The -E is for default authentication. The >> indicates to append the text to the file, you can also use > which will replace the entire contents, so be careful with this one.

To run the code, simple double-click the batch file. The command window will appear for a split second and close. If you want to stop the command window from closing add the following code to the batch file.

set /p delExit=Hit ENTER to exit...

If everything has worked you can open the text file and see the results of the SQL query.

SQL results text file

This was a very simple way to run a SQL query and write the results to a text file. Have a play with other commands or maybe the layout of the results. I have taken this concept and scheduled a task that will run once a day, allowing me to write stats to a text file every 24 hours. See what you can do!

See you next time, Ben.