Executue SQL Query using Batch


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 helps to execute SQL Queries.

Batch utility provides the sqlcmd 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 sqlcmd interactively, or to build script files to be run using sqlcmd, users must understand Transact-SQL.

sqlcmd options

sqlcmd 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 sqlcmd connects.
  2. Authentication options (-E, -U, and -P) specify the credentials that sqlcmd 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 sqlcmd.
  4. The output option (-o) specifies the file in which sqlcmd is to put its output.

Syntax:

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

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  
 sqlcmd -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
Kailash Chandra Behera


No comments:

Post a Comment