Execute SQL Script using batch file
A batch file is
a type of script file, a text file containing a series of commands to be
executed by the command line interpreter. A batch file may
contain any command the interpreter accepts interactively at the command prompt
.
Below is an example of how to run a set of SQL queries using batch file
First we will create a sample database and a table and we will
truncate that table using a batch file.
--Below is the table and test script
create database test
create table test
(id int )
insert into test values (1),(2),(3)
Now we will truncate the table using batch file. For that we
will write the query as per the requirement. Here I will show an example by
truncating table.
truncate table test
First Step
Save the above truncate query to any of your folder with .sql extension in my case I am
saving the sql file in C drive CDE folder.
Note: Don’t save the SQL file directly into C drive as some
time it can throw an error due to permission issue in C drive.
Second Step
Create a notepad file write the below code.
echo off
sqlcmd -E -S localhost -d test -i C:\CDE\Truncate_Test.sql
set /p delExit=Press the ENTER key to exit...
Save the above notepad file in any name and save it to any location with .bat
extension.
Explanation of above script
-S Server Name which is Localhost-Local SQL
Server instance Name in our case.
-E Trusted Connection or Windows Connection as I am
using window connection.
-D Database name Test-DB Name
-I Input file C:\CDE\Truncate_Test.sql is the path where you have saved your SQL Code.
In case you are using mix mode authentication then use below
one.
-U login Id /User Name
-P Password
Double click on your batch file and it will run the SQL file
and will truncate your table with the below screenshot.
Advantages of Batch file
You can perform many operations using Batch file like Running SQL Scripts using SQL Files like deleting data from
tables clearing log files on monthly basis, copy files and a lot.
No comments:
Post a Comment