How to run multiple SQL scripts using a batch file?

Question

I have a case where i have got 10+ SQL script.

I don't want to go and run all my scripts 1 by 1.

Is there a way that i can run all my scripts in succession in SQL Management studio.

I found this post. Creating a batch file seems easier.

This is all you need:

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


for %%f in (*.sql) do (
 (
sqlcmd.exe  -S servername -E   -d databasename -i %%f >>output.txt
)


pause

Replacing servername and databasename, but it seems to be not working.

Any ideas?

Bespoke

What appears in output.txt when you run the batch file? – Carcinomatosis

@MarkBannister Willem started the batch process at 10:50:30.46. Then nothing else happens – Bespoke

What output do you get if you remove the @echo off command from the batch file and try again? Also, try adding ECHO %PATH% to the start of the batch file and check that the path for SQLCMD.EXE is included. – Carcinomatosis

@MarkBannister Willem started the batch process at 11:18:51.16. – Bespoke

How about the path - did it include the path for SQLCMD.EXE? Additionally, what happens if you try running the batch command interactively, in a command prompt? – Carcinomatosis

Also, have you tried removing the brackets and including the sqlcmd.exe on the same line as the do? I notice that you appear to have an unclosed parenthesis in your script. – Carcinomatosis

@MarkBannister I just double clicked the batch file. It created the output file and then nothing happened... So it seems to not pickup the files in the folder. – Bespoke

You probably already know this, in case you don't, the script you posted will only run on machines that have SQL server installed. – Herbivore

Answer 1

You can create a Strored Procedure to call all your Scripts. You could also create a schedule plan to run the scripts automaticaly.

http://msdn.microsoft.com/en-us/library/aa174792(v=sql.80).aspx

Cellophane

Do you got an error message from the script? Have you checked the path for '*.sql', which represents the sql scripts you want to run. – Cellophane

Only a thought: Try to delete the blanks between the option and the parameter – Cellophane

Still nothing. It just seems to skip all the files... Is the syntax even right? – Bespoke

The syntax seems to be allright, try the following ECHO %USERNAME% started the batch process at %TIME% >output.txt for %%f in (*.sql) do ( ( ECHO %%f >>output.txt ) Run this script and see, if all of your sql files are listed in otuput.txt (with full path?) – Cellophane

Nope, only writes Willem started the batch process at 11:18:51.16 – Bespoke

It seems your for- command didn't work. See this page to fix this problem. After that we could go one with the sql-scripts – Cellophane

Answer 2

You've got an unmatched parenthesis, there. Try

for %%f in (*.sql) do sqlcmd.exe -S servername -E -d databasename -i %%f >>output.txt

I just saved it in a .cmd file and it appears to be working.

Leighannleighland

Answer 3

Yes, it's possible. You can do it with :r command of SQLCMD.

I strongly recommend you to read this article and do it with SQLCMD

http://www.mssqltips.com/sqlservertip/1543/using-sqlcmd-to-execute-multiple-sql-server-scripts/

Sayette

Is this possible to run all script in transaction? and if any error in any script then rollback and stop execution. – Detainer

Answer 4

Here is an open source utility with source code http://scriptzrunner.codeplex.com/

This utility was written in c# and allows you to drag and drop many sql files and start running them against a database.

Vinery

Answer 5

Some batch trick

cd %~dp0 //use this if you use 'for xxx in', it solved most of my problems 

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


for %%f in (*.sql) do (
(
sqlcmd.exe  -S servername -E -d databasename -i %%f >>output.txt
)
echo %errorlevel%
pause

Delanadelancey

Answer 6

You can use Batch Compiler add-in for SMSS, it let's you run multiple scripts at once, create SQLCMD scripts or consolidate them into a *.sql file.

Testify

Answer 7

If you want to run Oracle SQL files through a Batch program, then the code below will be useful. Just copy & change the Database credential and DB names

@echo off
for %%i in ("%~dp0"*.sql) do echo @"%%~fi" >> "%~dp0all.sql"

echo exit | sqlplus scott/tiger@orcl @"c:\users\all.sql"
pause

Basically, you need to put this batch file in the folder where you have all the SQL files. It will first get all the sql file names in the directory and load their full path with the sql file names. Then, it will write into a file all.sql and then sqlplus will call that all.sql to execute all the sql files that you have in that directory.

Crosscut