In this tutorial, I’m going to show you how to create a batch file to export SQL query results to a text file.
While this article focuses on SQL Server, you may want to check the following source that explains how to use Spool in Oracle to export your query results.
To start, I’ll review a simple example to demonstrate how to export SQL query results to a text file.
Let’s say that you have a database in SQL Server, where:
- The database name is: TestDB
- The table name is: dbo.Person
- The dbo.Person contains the following data:
The goal is to export the results of the following SQL query using a batch file:
select * from TestDB.dbo.Person
This is the data that you’ll get when running the above query in SQL Server:
Let’s now review the steps to export the SQL query results.
Steps to Create Batch File to Export SQL Query Results to a Text File
Step 1: Prepare the command to export the query results
You may use the following template to export the query results to a text file:
sqlcmd -S ServerName -d DatabaseName -E -Q " theQuery " -s "," -o "Path to store the exported file\FileName.txt"
For our example:
- The server name that I used is: RON\SQLEXPRESS
- The database name is: TestDB
- The query is: “select * from TestDB.dbo.Person“
- The Path to store the exported file\FileName.txt is: “C:\Users\Ron\Desktop\Export.txt”
So this is the code that I used for our example:
sqlcmd -S RON\SQLEXPRESS -d TestDB -E -Q "select * from TestDB.dbo.Person" -s "," -o "C:\Users\Ron\Desktop\Export.txt"
You’ll need to adjust the code to fit your server name, and any other parameter, such as your database name, and the location where the TXT file will be saved on your computer.
Later, I’ll further explain each component in the above command, just in case you need additional clarifications.
Step 2: Create the Batch file
Copy the above command into Notepad (after making the adjustment to your server name, export location, etc.)
This is how the Notepad looked like in my case:
You’ll then need to save the Notepad file with the suffix extension of “.bat”
Here, I chose to save the file as “export.bat”
Once you are done, click on the Save button, and a new batch file will be created at your specified location:
Step 3: Run the batch file
For the final step, double-click on your batch file, and a new TXT file with the query results will be created:
Additional explanation about the components of the code
Now let’s look closer at each component of the code, so that you can adjust it to fit your parameters:
• sqlcmd is the command
• -S stands for the server name that you connect in SQL Server. Here, my server name is RON\SQLEXPRESS
• -d is the database name. For our example, the database name is TestDB
• -E stands for trusted connection
• -Q is the place where you’ll need to put your query in quotations: “select * from TestDB.dbo.Person”
• -s is for the column separator. In our case, it is going to be comma “,”
• -o is the location where the file will be saved (as well as the type of file that will be saved at that location. In our case, it is a TXT file): “C:\Users\Ron\Desktop\Export.txt”
You may also find it useful to get a legend for the command sqlcmd by typing the following command in the Command Prompt:
You just saw how to export SQL query results to a text File. This method would work when dealing with SQL server. Yet, if you’re dealing with Oracle, you may want to check the following tutorial that explains how to export SQL query results using Spool.
Batch files are indeed useful to work with. You may then want to visit the following tutorials that deal with Batch files:
- Generating backup files with timestamps
- Creating the Matrix effect
- Creating a batch file to run Python script
Finally, you can read more about sqlcmd by visiting the sqlcmd utility documentation.