How to execute multiple scripts

Question

Post reply

How to execute multiple scripts

jagjitsingh

Ten Centuries

Points: 1059
More actions
December 6, 2017 at 7:58 am

#409830

Hi
How to execute single file multiple scripts in one go.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113445 More actions · Answer 1

jagjitsingh - Wednesday, December 6, 2017 7:58 AM
Hi
How to execute single file multiple scripts in one go.
Thanks

Red-Gate Multi Script, https://www.red-gate.com/products/dba/sql-multi-script/, works a charm

Or if you don't want to pay, your going to have to find some way to concatenate the scripts into 1 large script. Done it in the past with command line and the COPY command

COPY C:\Scripts\*.sql C:\Scripts\BigScript.sql

Problem is ensuring they are all saved correctly as you can get some strange encoding happening some times which throws the big script off.

Sue_H SSC Guru Points: 90860 More actions · Answer 2

I'm not sure what you mean by "single file multiple scripts" but to execute multiple scripts in a single step, you could use sqlcmd
You can create a main script that uses sqlcmd to execute the scripts - it would just be something like:
:r c:\ScriptsDirectory\fileA.sql :r c:\ScriptsDirectory\fileB.sql :r c:\ScriptsDirectory\fileC.sql :r c:\ScriptsDirectory\fileD.sql

And then execute the main script from the command line with sqlcmd with the input being the main script.
Or you can just execute that list in SSMS by changing the mode to sqlcmd.

Sue

jagjitsingh Ten Centuries Points: 1059 More actions · Answer 3

Sue_H - Wednesday, December 6, 2017 8:17 AM
I'm not sure what you mean by "single file multiple scripts" but to execute multiple scripts in a single step, you could use sqlcmd
You can create a main script that uses sqlcmd to execute the scripts - it would just be something like:
:r c:\ScriptsDirectory\fileA.sql :r c:\ScriptsDirectory\fileB.sql :r c:\ScriptsDirectory\fileC.sql :r c:\ScriptsDirectory\fileD.sql
And then execute the main script from the command line with sqlcmd with the input being the main script.
Or you can just execute that list in SSMS by changing the mode to sqlcmd.
Sue

Hi

Yes i have script files like below there are 100 & so . Instead of running separately i want all to be executed in one go.
c:\ScriptsDirectory\fileA.sql
c:\ScriptsDirectory\fileB.sql
c:\ScriptsDirectory\fileC.sql
c:\ScriptsDirectory\fileD.sql
Thanks

RTaylor2208 SSChampion Points: 13298 More actions · Answer 4

One easy and free solution is to use a parameterised batch file (there are more elegant solutions), this is just a basic example.

Save the following as deploy.bat:
SET FOLDER=%1 SET SERVER=%2 SET DB=%3

for %%f in (%FOLDER%\*.sql) do sqlcmd -S %SERVER% -d %DB% -E -i %%f > %%f.log

Then Execute the batch file passing 3 parameters
deploy.bat C:\temp\Scripts MYSQLServer MySQLDB

Obviously you can (and should) add additonal logic for error handling, but as a start this will do what you need.

MCITP SQL 2005, MCSA SQL 2012

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

RTaylor2208 - Thursday, December 7, 2017 5:06 AM
One easy and free solution is to use a parameterised batch file (there are more elegant solutions), this is just a basic example.
Save the following as deploy.bat:
SET FOLDER=%1 SET SERVER=%2 SET DB=%3
for %%f in (%FOLDER%\*.sql) do sqlcmd -S %SERVER% -d %DB% -E -i %%f > %%f.log
Then Execute the batch file passing 3 parameters
deploy.bat C:\temp\Scripts MYSQLServer MySQLDB
Obviously you can (and should) add additonal logic for error handling, but as a start this will do what you need.

Nowadays, you can use the "kind of new" FORFILES command to make your life just a little easier.
https://technet.microsoft.com/en-us/library/cc753551(v=ws.11).aspx

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)