Pages - Menu

Thursday, March 3, 2016

Batch Script to execute multiple SQL files

If we are working on a project and we need to execute multiple .sql files then executing these files one by one is very tedious work. There are multiple options available to execute the .sql files, we can open the .sql file in SQL Server Management Studio and execute it or to write a batch script to execute all the files each time if the deployment moves to another environment.

So, here is the batch script which is dynamic in nature so that we can easily use this script across any SQL Script files.

This batch file is capable of executing all the .sql files in a folder which contains sub directory or not. It executes the scripts in alphabetical order, so our main task is to order the files based on the dependency.

Example: 

If you have script to create the tables and then insert the data in to that table, then table should be created first then only you can insert the data.

To get this work done, you can create the folders like.











Batch Script:


@echo off

set /p servername=Enter DataBase Servername :
set /p dbname=Enter Database Name :
set /p username=Enter Username:
set /p password=Enter Password :
set /p spath=Enter Script Path :
set hr=%time:~0,2%
if "%hr:~0,1%" equ " " set hr=0%hr:~1,1%
set logfilepath= %spath%\output_%date:~-4,4%%date:~-10,2%%date:~-7,2%_%hr%%time:~3,2%%time:~6,2%.log
set cmd='dir %spath%\*.sql /b/s'
FOR /f %%G IN (%cmd%) DO (
echo ******PROCESSING %%G FILE******
echo ******PROCESSING %%G FILE****** >> %logfilepath%

sqlcmd -S %servername%\SQLSERVER -U %username% -P %password% -d %dbname% -i"%%G">> %logfilepath%

IF !ERRORLEVEL! NEQ 0 GOTO :OnError
)
GOTO :Success

:OnError
echo **********ERROR******************
echo One\more script(s) failed to execute, terminating path.
echo Check output.log file for more details
EXIT /b

:Success
echo ALL the scripts deployed successfully!!
EXIT /b

Batch Script to execute multiple SQL files
















Copy the above script and paste it in a notepad and save it as Batch.bat. Now execute the batch and provide the parameters, it will execute all the files from the folder specified. This batch file will create a output log file with all the information about which script it executed and it's result.

Note: Your folder name should not contains the space.


No comments:

Post a Comment