Executing sql scripts in direcory using .bat file

If you are in situation where you have to execute all sql scripts located as separate files in one folder, executing .bat file is the one of the options you can use. Executing batch command is quite flexible solution as you don’t have to recompile it every time the script will change.

The code below loops through all files within the specified directory, loads each of them into variable and replaces the param string if exists before execution. It is helpful if you need to replace database specific users (e.g. execute with ‘username’) etc.

Please note that variables within the loop are accessed by !myVar! instead of %myVar% because of delayed expansion applied.

Command cd /d %1 sets the current directory to the specified by the user in first parameter.

 @ECHO OFF
 echo script folder: %1
 echo server: %2
 echo database: %3
 echo database user: %4
 echo ----------------

setlocal enabledelayedexpansion
cd /d %1

for %%d in (*.sql) do (
   set filecontent=
   for /f "delims=" %%a in (%%d) do (set "filecontent=!filecontent!%%a")

   set "temp=!filecontent!"

   set "filecontent=!temp:#PARAM#=%4!"

   echo executing %%d
   echo ----------
   sqlcmd -S%2 -E -Q"!filecontent!" -d%3 
)
pause

Above code can be executed as follows (trusted database connection is used)

 C:\updateDB.bat "C:\scripts\" "myServer" "databaseName" PARAM_TO_REPLACE