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
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.
Create a notepad file write the below code.
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
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.