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?

@MarkBannister Willem started the batch process at 10:50:30.46. Then nothing else happens — Willem, Jan 13 '12 at 09:13
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. — , Jan 13 '12 at 09:18
@MarkBannister Willem started the batch process at 11:18:51.16. — Willem, Jan 13 '12 at 09:19
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? — , Jan 13 '12 at 09:45
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. — , Jan 13 '12 at 09:47
@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. — Willem, Jan 13 '12 at 09:49
You probably already know this, in case you don't, the script you posted will only run on machines that have SQL server installed. — Arcturus, Aug 07 '12 at 19:13

score 9 · Answer 1 · answered Feb 07 '12 at 18:39

9

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.

answered Feb 07 '12 at 18:39

Galdur

131
4

score 5 · Answer 2 · answered Jan 13 '12 at 07:30

5

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/

answered Jan 13 '12 at 07:30

Chuck Norris

15,207
15
92
123

1

Is this possible to run all script in transaction? and if any error in any script then rollback and stop execution. – Rikin Patel Jun 20 '12 at 07:13

score 3 · Accepted Answer · answered Jan 13 '12 at 07:33

3

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

answered Jan 13 '12 at 07:33

Andreas Rohde

613
5
15

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. – Andreas Rohde Jan 13 '12 at 09:06
Only a thought: Try to delete the blanks between the option and the parameter – Andreas Rohde Jan 13 '12 at 09:24
Still nothing. It just seems to skip all the files... Is the syntax even right? – Willem Jan 13 '12 at 09:32
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?) – Andreas Rohde Jan 13 '12 at 09:35
Nope, only writes `Willem started the batch process at 11:18:51.16` – Willem Jan 13 '12 at 09:43
It seems your for- command didn't work. See this [page](http://ss64.com/nt/for2.html) to fix this problem. After that we could go one with the sql-scripts – Andreas Rohde Jan 13 '12 at 09:48

Clinton Ward · Answer 4 · 2013-06-27T07:30:11.757

2

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.

edited Jun 27 '13 at 07:30

answered Mar 05 '12 at 08:05

Clinton Ward

2,441
1
22
26

innuendomaximus · Answer 5 · 2017-01-01T04:08:22.700

0

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

edited Jan 01 '17 at 04:08

answered Jul 07 '15 at 12:30

innuendomaximus

353
2
3
17

score 0 · Answer 6 · answered Jul 20 '15 at 17:12

0

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.

answered Jul 20 '15 at 17:12

Patricio Cobein

49
1
6

score 0 · Answer 7 · edited Oct 06 '20 at 10:32

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.

How to run multiple SQL scripts using a batch file?

7 Answers7