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

In this article, I’m going to show you how to create a batch file to export SQL query results to a Text file. I will also demonstrate how to add a button in MS Access 2016 to run the batch script.

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 SQL query results.

To start, I’ll review a simple example to demonstrate how you can 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

This is the data within the dbo.Person table:

NameAgeCity
Jade20London
Mary119NY
Martin25London
Rob35Geneve
Maria42Paris
Jon28Toronto

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

select * from TestDB.dbo.Person

This is the data that you’ll get when running the above query:

SQL query results

Let’s now review the steps to export the SQL query results to a text file.

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

(1) To start, open the Command Prompt:

 

Command Prompt

 

(2) Then, type the following command: sqlcmd /?

This will give you a legend, for the command sqlcmd, which you can then reference when creating the batch command:

 

sqlcmd

 

(3) Now, type the below command, and then press Enter (explanation to be provided below). Please note, that you may 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.

sqlcmd -S DORON\SQLEXPRESS -d TestDB -E -Q " select * from TestDB.dbo.Person " -s "," -o "C:\Users\Doron E\Desktop\Export.txt"

How to Export SQL Query Results to Text File

Next, I’ll explain 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 DORON\SQLEXPRESS

 

Server name - SQL Server

 

• -d is the database name. My 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”

This is how the query would look like in SQL Server:

 

Select query - SQL Server

 

• -s is for the column separator. In our case, it is going to be a 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\Doron E\Desktop\Export.txt”

Now let’s see how you can create the actual batch file to export the SQL query results to a Text file.

Create the Batch file

(1) First, open Notepad and then copy the following code (adjusted to your server name, and any other parameter, such as your database name, etc…):

@echo off
sqlcmd -S DORON\SQLEXPRESS -d TestDB -E -Q " select * from TestDB.dbo.Person" -s "," -o "C:\Users\Doron E\Desktop\Export.txt"

batch file to export SQL query

 

(2) Next, 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

 

(2) Double-click on your batch file (i.e., the export.bat file), and your TXT file with the query results will now get generated:

 

TXT file with the SQL query results

 

In the last section, I’m going to show you how to add a button in MS Access 2016 to run the batch script from Access itself.

Add a button in MS Access 2016 to run the batch script

To call the Batch script from Access, you’ll need to use the “Shell” command.

Let’s review the steps:

(1) In MS Access 2016, go to the Create tab, and then press on the Form Design to create a new form, where you will place your button:

 

Create Tab in MS Access - Form Design

 

(2) Under the Design tab, press on the “button shape” and then place your button on the form itself:

 

Add a button in MS Access 2016 to run the batch script

 

(3) Since we won’t be using the Command Button Wizard, then press Cancel:

 

Command Button Wizard

 

(4) On the Property Sheet, select “[Event Procedure]” for the On Click option. Then, press on the 3 dots (“…”)

Please note that if you can’t see the Property Sheet, you can always populate it on your screen by pressing “F4” while in the Design or Layout Views.

 

Property Sheet - MS Access

 

(5) Type the following code, which includes the Shell command to run the batch script when pressing the button:

 

VBA Shell command to run the batch script when clicking on the button

 

Let me explain the components in the brackets for the Shell command:

"C:\Users\Doron E\Desktop\Batch\export.bat"

The following portion represents the location where I stored the batch file:  C:\Users\Doron E\Desktop\Batch\  

The following part represents the name of the batch file: export

Finally, the last portion represents the file type of: .bat

(6) Save your work and then switch to the Form View, where you’ll be able to press the button to run your batch script from Access:

 

Form View - MS Access

 

To take things one step further, you can even import your TXT file into Access (the TXT file will be created once you press on the button to run the batch script).

In fact, you can create another button (or use an existing one) to import your TXT file into Access using one click!

Additional Sources

We 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.

We also saw how to use a batch file to export those query results. 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.