Executue SQL Query using Batch

Kailash Chandra Behera | Sunday, March 25, 2018

Introduction

This following blow explains how to create a batch file to execute SQL query without using SQL Management Studio.

Getting Started

A batch file is a kind of script in DOX and Microsoft Windows. It can contain series of command to be execute by the command-line interpreter.

In this blow, we will discuss the commands with their syntax details which help to execute SQL Queries.

Batch utility provides the SQL command utility to execute SQL command, this is a command-line utility for ad hoc, interactive execution of Transact-SQL statements and scripts, and for automating Transact-SQL scripting tasks. To use SQL command interactively, or to build script files to be run using SQL command, users must understand Transact-SQL.

SQL command options

SQL command provides verite of options, which can be used to execute different command in SQL.
  1. Server option (-S) identifies the instance of Microsoft SQL Server to which SQL command connects.
  2. Authentication options (-E, -U, and -P) specify the credentials that SQL command uses to connect to the instance of SQL Server. NOTE: The option -E is the default and does not need to be specified.
  3. Input options (-Q, -q, and -i) identify the location of the input to SQL command.
  4. The output option (-o) specifies the file in which SQL command is to put its output.

Syntax:

 <h3 style="text-align: justify; text-justify: inter-ideograph;color: #20124d;">Syntax:</h3>

Example:

This following example executes a SQL file 'SP_SetCity.sql' which exist in DBScripts folder. The SP_SetCity.sql file conatins following below SQL codes which will execute by batch file.
 CREATE STOREPROCEDURE SP_SetCity  
 AS  
 BEGIN  
 CREATE TABLE CityMaster  
 (  
 ID INT ,  
 Name NVARCHAR(100)  
 )  
 INSERT INTO CityMaster VALUES(1,'MUMBAI')  
 INSERT INTO CityMaster VALUES(1,'DELHI')  
 INSERT INTO CityMaster VALUES(1,'CHENAI')  
 INSERT INTO CityMaster VALUES(1,'KOLKATA')  
 END
This below are the contents of batch file, which executes above mentioned SQL file. The contents has SQL server information Like server name, database name, credentials of SQL server and log file path. _Deploy.txt is the log file where batch will write log details of execution.

 @ECHO off  
 set /p choicedatabase=DO YOU WANT TO UPDATE DATABASE (y/n) ?  
 set /p SName=KCB-5 :  
 if '%choicedatabase%'=='y' goto begin  
 goto end  
 :begin  
 if exist _Deploy.txt del _Deploy.txt   
 @echo on  
 SQL command -S %SName% -U "kcb" -P "kcb@123" -d "TestDB" -I -i DBScripts/SP_SetCity.sql >> _Deploy.txt 2>&1  
 @notepad _Deploy.txt  
 setup.exe  
 exit  
 :end  
 setup.exe  
 exit

Thanks

By at