Create Batch File to Export SQL Query Results to a Text File

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.

The Example

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:
NameAgeCity
Jade20London
Mary119NY
Martin25London
Rob35Geneva
Maria42Paris
Jon28Toronto

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:

SQL query results

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:

Create Batch File to Export SQL Query Results to a Text File

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

Create Batch file to Export SQL Query Results to a Text File

Once you are done, click on the Save button, and a new batch file will be created at your specified location:

batch file

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:

TXT file with the SQL query results

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

Server name - SQL Server

• -d is the database name. For our example, the database name is TestDB

Database name - SQL Server

• -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:

sqlcmd/?

sqlcmd

Additional Resources

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:

Finally, you can read more about sqlcmd by visiting the sqlcmd utility documentation.