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

In this tutorial, you’ll see how to create a batch file to export SQL query results to a text file.

The Example

Let’s say that you have a database, where:

• The database name is: test_database
• The table name (including the schema) is: dbo.product
• The dbo.product table contains the following data:

The goal is to export the results of the following SQL query using a batch file:

SELECT * FROM test_database.dbo.product

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 in order 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 example, let’s say that you are given the following information:

• The server name is: RON\SQLEXPRESS
• The database name is: test_database
• The query is: “SELECT * FROM test_database.dbo.product“
• The path to store the exported file is: “C:\Users\Ron\Desktop\Example.txt”

Therefore, the complete command to export the query results is:

sqlcmd -S RON\SQLEXPRESS -d test_database -E -Q "SELECT * FROM test_database.dbo.product" -s "," -o "C:\Users\Ron\Desktop\Example.txt"

Note that you’ll need to adjust the command to fit your server name, and any other parameter, such as your database name, and the location where the TXT file will be stored on your computer.

Step 2: Create the Batch file

1. Copy the above command into Notepad (after making the adjustments to your server name, path, etc).
2. Then, save the Notepad file with the suffix extension of “.bat” in order to create the batch file. For example, let’s save the Notepad as “Export.bat“
3. 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:

The components of the code

Now let’s take a closer look at each component of the code:

• sqlcmd is the command
• -S stands for the server name. Here, it is RON\SQLEXPRESS
• -d is the database name. For our example, the database name is test_database
• -E stands for trusted connection
• -Q is the place where you’ll need to put your query in quotes: “SELECT * FROM test_database.dbo.product“
• -s is for the column separator. In our case, it is comma “,”
• -o is the location where the file will be saved: “C:\Users\Ron\Desktop\Example.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/?

Additional Resources

You may want to visit the following tutorials about batch files:

• Generating backup files with timestamps
• Creating a batch file to run a Python script