THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Merrill Aldrich

How to Run a Series of T-SQL Scripts in a Specific Order

Another post in the handy-but-not-bleeding-edge category.

In the past few months I’ve seen a number of folks struggle with how to reliably/repeatedly execute a heap of T-SQL Script files, in order. One could certainly argue about why there’s the need for piles of scripts in text files, but that’s outside the scope of this post – today I want to focus on how to “get 'er done,” and save that philosophical discussion for another time.

Problem: Heap o’ Script Files

You receive a folder full of scripts from (who else) your ISV, together with a list of the files in the order they should be executed. This is obviously fraught with opportunities for error if one were to manually execute them each, one at a time.

Workaround: SQLCMD and :r

Thankfully, there’s a very easy way to encapsulate this with a SQLCMD “master” script to call all the individual files, predictably, in the correct order. The :r construct in SQLCMD is a file include, which means you can easily make a script that calls another text file, or many other text files, from disk.

First, copy your ordered file list and paste it into a query window. Be careful to avoid whitespace before and after each line of text:

somefile.sql
another file.sql
thirdfile.sql

Set the query to SQLCMD mode.

Next, use a regex find/replace to

  • Prepend each line with the text :r "yourFilePath\
  • End each line with a double-quotation mark, "
  • Add a GO batch terminator between each line

That is, find any line containing some text, using this regex:

^{.+}

Then Replace All using something like:

:r "c:\\some Folder\\\1"\nGO

The \1 within the replace expression will be populated with the original text from each line, which will, in effect, surround the original text with the quotation marks, file path, and GO. The extra backslashes are required to escape special characters including newline and backslash itself.

The resulting script should look like this:

:r "c:\some Folder\somefile.sql"
GO
:r "c:\some Folder\another file.sql"
GO
:r "c:\some Folder\thirdfile.sql"
GO

Be careful with subtleties like whitespace around the file names – regex is tough to read and get correct, so it’s easy to make an error. It might take a couple of tries.

At the top of the script, again, you may also want this line:

:ON Error EXIT

This will cause the client to abort on any error from the scripts, instead of continuing.

Check, triple check, quadruple check your work, and you should have a meta-script that will call all the SQL files in order.

Cheers, and happy scripting!

Published Monday, September 12, 2011 10:36 AM by merrillaldrich
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

JeBrewer said:

Nice, this will save me a few headaches!

September 13, 2011 2:15 PM
 

Gary said:

Hi,

Thanks a lot.  This will help.  I have created a bunch of small scripts to do a repetitive job.  They are small to modularize various functions.  And some use features of SSMS so they can't be in sprocs.

April 5, 2012 4:46 PM
 

JD said:

Very nice, just saved me a load of work, thanks

June 5, 2013 6:42 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement