Introduction
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.
 |
| 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.
-S Specifies the instance of SQL Server to which to connect. It sets the sqlcmd scripting variable SQLCMDSERVER.
-d Issues a USE db_name statement when you start sqlcmd.
-U Is the login name or contained database user name. For contained database users, you must provide the database name option (-d).
-P Is a user-specified password. Passwords are case-sensitive.
-i Identifies the file that contains a batch of SQL statements or stored procedures.
-o Identifies the file that receives output from sqlcmd.
-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
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
No comments:
Post a Comment