Run SQL Script from Batch File

Batch files are often used to help load programs, run multiple processes at a time, and perform common or repetitive tasks.

Here in this blog, we are going to discuss how to create batch file, write batch file, batch file commands to execute SQLCOMMAND or SQLCOMMANDS and pass parameters to SQL query from bat file.

Run SQL Script from Batch File

Windows Batch File Example(bat file)

Getting Started

A batch file or windows batch file contains batch script file (windows batch script) which consists of series of batch commands to be executed by command line interpreter.

The windows notepad can be used to create batch file, you just need to save the notepad with .bat extension after writing the batch programming.

Batch Script Example

The below code one of the batch files examples which contains simple batch script commands to display Hello World message.

 @ECHO OFF:: start command in batch file  
 ECHO Hello World!  
 PAUSE  

windows batch script example

The above batch script example displays “Hello World!”. prompts and waits for the user to press a key, and then terminates. To execute the file, it must be saved with the filename extension suffix .bat

Note that it does not matter if commands are lowercase or uppercase unless working with variables.

Above are the basic idea about the bat file or batch file or Windows batch file, our main motive is how to create batch file that will execute a SQL query. Below details are for about execute SQLCommand through batch script or batch command.

Execute SQLCommand

The following bat file (Batch file) contains batch commands (batch scripts) to execute the MS SQL Commands. This batch file reads the SQL query from a local SQL file and executes it with the help of SQLCMD batch command.

This command would execute a SQL script on a SQL server using SQL authentication. Multiple MS SQL Commands can be executed by using multiple SQLCMD batch commands in a single bat file. Copy the above code

 @ECHO off  
 sqlcmd -S %ServerName% -U "%UserName%" -P "%Password%" -d "%DatabaseName%" -I -i SQLScript.sql  
 pause  

Batch Command to Execute SQLCommand

-S, -U, -P and -d are command line options used with SQLCMD batch command to execute sql queryies. See the below details for more information.

1. -S Specifies the instance of SQL Server to which to connect. It sets the sqlcmd scripting variable SQLCMDSERVER.
2. -d Issues a USE db_name statement when you start sqlcmd.
3. -U Is the login name or contained database user name. For contained database users, you must provide the database name option (-d).
4. -P Is a user-specified password. Passwords are case-sensitive.
5. -i Identifies the file that contains a batch of SQL statements or stored procedures.
6. -o Identifies the file that receives output from sqlcmd.
7. -v Creates a sqlcmdscripting variable that can be used in a sqlcmd script.

To execute the file, it must be saved with the filename extension suffix .bat in plain text format, typically created by using a text editor such as Microsoft Notepad.

Execute SQL Commands with Parameter

Parameters can be passed to SQL Query using the batch file arguments. The following batch script passes a parameter to SQL query.

 @ECHO off  
 sqlcmd -S %ServerName% -U "%UserName%" -P "%Password%" -d "%DatabaseName%" -I -i SQLScript.sql -v input=%ndays%  
 pause  

Batch File Parameters Example

In the above code new command line option (-v) is used to pass commad line argument to SQL query. You can use multiple -v as like below syntax to pass multiple parameter in to the SQLCMD.

 sqlcmd -v <name of variable>=<value> -v <name of variable>=<value>  

Example

 @ECHO off  
 sqlcmd -S %ServerName% -U "%UserName%" -P "%Password%" -d "%DatabaseName%" -I -i SQLScript.sql -v input1=%ndays1% -v input2=%ndays2%  
 pause  

SQL File Codes

  
 DECLARE @Name NVARCHAR(100)='$(input)'  
 SELECT * FROM StudentDetails WHERE SName=@Name  

Execute SQLCommand and store it Outputs in a File

The below code example executes an SQL command and stores the result in a text file.

 @ECHO off  
 sqlcmd -S %SName% -U "%UName%" -P "%Pass%" -d "%DName%" -I -i %0\..\Student.sql -v input=%StName% >> _Result.txt 2>&1  
 echo SQLCommand execution completed...  
 @notepad _Result.txt  
 setup.exe  
 exist  
 exit  

Above batch script executes SQL query from student.sql file and stores the SQL output result in a text file called _Result.txt.

Demonstration

The below given codes the bat file code and SQL file code, can be used for a demonstration to see how to execute a SQL Command by bat file using batch script. Copy the bat file codes, paste it in a notepad and save it with extension .bat.

Bat File Code

 @ECHO off  
 TITLE Demonstration-Run SQL Script from Batch File   
 color a  
 ::DESKTOP-65KMK07  
 ECHO Enter the SQL Server Name(case sensitive)  
 SET /p SName=SQL Server Name :  
 ECHO Enter the Database Name(case sensitive)  
 SET /p DName=Database Name :  
 ECHO Enter the SQL Server User Name(case sensitive)  
 SET /p UName=User Name :  
 ECHO Enter the SQL Server Password(case sensitive)  
 SET /p Pass=Password :  
 ECHO Enter the Student Name  
 SET /P StName=Student Name :  
 IF EXIST _Result.txt del _Result.txt  
 ECHO Excuting SQLCommand.............  
 ECHO.  
 sqlcmd -S %SName% -U "%UName%" -P "%Pass%" -d "%DName%" -I -i %0\..\Student.sql -v input=%StName% >> _Result.txt 2>&1  
 ECHO.  
 echo SQLCommand execution completed...  
 @notepad _Result.txt  
 setup.exe  
 exist  
 :end  
 exit  

Batch File To Run SQL Script

Copy the below SQL code and execute it in your database. This will create a table having name StudentDetails. Insert some data into the table.

 CREATE TABLE [dbo].[StudentDetails](  
      [ID] [int] IDENTITY(1,1) NOT NULL,  
      [SName] [nvarchar](100) NULL  
 ) ON [PRIMARY]  

SQL File Code

Copy the below SQL code and save it in the same location where the bat file exits. the bat file will execute the sql queries from this file.

  
 DECLARE @Name NVARCHAR(100)='$(input)'  
 SELECT * FROM StudentDetails WHERE SName=@Name  

Finally run the bat file in administrator mode, the command prompt window will open like the image given above. enter the required values and press enter. At the end, the command prompt will open a notepad with sql result like below image.

Windows Batch File Example

Bat File output to the Text file

Thanks