SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to execute multiple scripts


How to execute multiple scripts

Author
Message
jagjitsingh
jagjitsingh
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 80
Hi

How to execute single file multiple scripts in one go.

Thanks
anthony.green
anthony.green
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57257 Visits: 8463
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.



How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Sue_H
Sue_H
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30430 Visits: 8803


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
jagjitsingh
SSC-Enthusiastic
SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)SSC-Enthusiastic (151 reputation)

Group: General Forum Members
Points: 151 Visits: 80
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
RTaylor2208
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6292 Visits: 1301
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
Jeff Moden
SSC Guru
SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)SSC Guru (463K reputation)

Group: General Forum Members
Points: 463754 Visits: 43803
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search