How to run multiple SQL scripts automatically in order.

If you’ve been working towards a new deployment to a live database chances are you have written several scripts (possibly dozens) that have been developed/tested against the the development server.

Now the time has come to put the update live. Which would require executing each script against the live database.

This task can be automated by using a very handy batch script to run against the directory the files are saved in.

Caveat: This process does not take into account error handling or rollbacks, it’s just a simple example people can build on.

In order for this to work the files must have been named in a manner that the necessary order of execution corresponds to ascii sort order, i.e. 001_CreateTable.sql, 002_PopulateTable etc. This is standard practice for sql file naming conventions.

Simply create a .BAT file with the following command:
(Swap servername and databaseName for your required server and database names, TIP: SELECT @@servername can provide you with the full server name.)

for %%G in (*.sql) do sqlcmd /S servername /d databaseName -E -i"%%G"

Place this .BAT file in the directory from which you want the .SQL files to be executed, double click the .BAT file and the command will loop and execute every SQL script in the folder.

2 thoughts on “How to run multiple SQL scripts automatically in order.

  1. Hi Rajasekhar, There’s an example of looping though sub folders via batch in this post.
    You could adapt the batch scripts, combing the logic of both, to execute files within sub folders but I’d recommend using the new script provided as is to pull all the files into a single folder and renaming the files so they will execute in sequence.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s