CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlbatch-file
peter
peter

Reputation: 2516

Executing SQL Scripts in a batch

I have about 100 scripts that needs to be executed on SSMS. How can i execute them as a batch instead of executing each individually.

I tried using this but it didn't work.

@echo off
ECHO %USERNAME% started the batch process at %TIME%  >output.txt


FOR %%? in ("1.01*.SQL") DO 
 (
sqlcmd.exe  -S local -E   -d collect -i %%? >>output.txt
)

pause

Upvotes: 0

Views: 1767

Answers (2)

Hank Freeman
Hank Freeman

Reputation: 1212

To all:

I found some issues with the above, Like Windows 7 Dos did not like the %%i or %%a so I removed an '%' character an then the dos script started to work just fine.

Below is my actual running version of the script where the "1.A00*.SQL" script run truncates my target tables.

The "A*.SQL" run script A01_name.sql --> A24_name.sql sequentially, which is quite nice

Hank Freeman [email protected]

end.

REM " FSA_3.2.2_Runner_for_Insert_Scripts.bat

Rem " Script runner "

Rem " How to use this script"

Rem " From a Remote Desktop session Open a Dos Prompt"

Rem " Run the below script"

Rem " Validate the data has been inserted."

Rem " Hank Freeman July 15th 2014 [email protected]"

Rem " * begin *"

c:

cd \

cd C:\Database_Scripts\Insert_Scripts

@echo off

if exist *.log ( del *.log )

rem "** Starting here *"

rem "run the Truncate SQL file"

for %i in (1.A00_*.SQL) do osql -E -i"%i" -m0 -w500 -I -b -r -o"%i.log" 2>%i.err.log

rem "run the A01-A24 SQL file"

for %i in (A*.SQL) do osql -E -i"%i" -m0 -w500 -I -b -r -o"%i.log" 2>%i.err.log

rem Remove empty .err files (ie had no errors)

for %a in (*.err.log) do if %~za==0 del "%a"

rem "Remove the logs - Manual operation"

rem if exist *.log ( del *.log )

Rem " * end *"

@echo on

Upvotes: 0

Jon Egerton
Jon Egerton

Reputation: 41539

The following runs all scripts in a directory, output data to a .log file and errors to a .err.log file:

@echo off
echo "Enter Server"
set /p SERVER=

echo "Enter Database"
set /p DATABASE=

if exist *.log ( del *.log )

rem Process the files
for %%i in (*.sql) do osql -E -S %SERVER% -d %DATABASE% -i"%%i" -m0 -w500 -I -b -r  -o"%%i.log"  2>%%i.err.log

rem Remove empty .err files (ie had no errors)
for %%a in (*.err.log) do if %%~za==0 del "%%a"


pause

Note: An earlier version of this used sqlcmd however there proved to be issues with sqlcmd missing out data in the output logs.

Upvotes: 3

Related Questions