Batch File To Print Result of SQL to Text File
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
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.
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.