How to execute multiple SQL scripts

Published 02 June 09 10:45 AM | MartinBell

I was reminded by a former colleague of a technique we used to use for building databases from scripts. It is a method I have posted a couple of times when answering a newsgroup questions and I have seen it a couple of times elsewhere, but not that often so I though a post would be useful!

If for whatever reason you wish to execute all the scripts in a given directory you now have several options. You could use the copy command concatenate all the scripts together and execute the single file. e.g.

copy /b *.sql mega_script.sql

The scripts will be appended together in alphabetical order.

A tip here that I was given to me by
Tony Rogerson was to use /b otherwise you get a spurious character appended to the file which can cause problems.

If your scripts don’t have GO statements in them, this could potentially create a very large batch remember there is a limit of 65536 * network packet size (which has a default of 4096 bytes). (N.B. In the early versions of OSQL there was a problem where a batches would be truncated when the file was over a certain size. I am not sure if this is still the case but it did not occur with SQLCMD.)

To add go’s to each file before concatenating them, you could easily do the following:

echo GO > GO.txt
for %i in (*.sql) do copy /b %i+GO.txt %i
copy /b *.sql mega_script.sql

When you do this, you have to be careful of missing carriage returns at the end of a file. You may end up with something like
goGO appearing or GOSELECT.

You can get around this by just calling the scripts within the for loop e.g.

for %f in (*.sql) do SQLCMD -E -S "(local)" -i %f –d Adventureworks

The problem with this example is when you want it to stop after a script when there is an error. The –b option for OSQL or SQLCMD returns the error level (1) to the command prompt. You can then use the execute on failure option (||)  to execute a second command, which can be the exit command. Unfortunately using the exit will close the command window, but you can get around this by putting everything into a batch file and using the exit command’s own /b option.

e.g. if b.bat contains the line:

for %%f in (*.sql) do SQLCMD -E -S "(local)" -i %%f -o %%~nf.out –b –d Adventureworks || exit /b

( Remember that in a batch script variables require a double % for variables! )

This script will also create a separate output file for each sql script, with a .out extension e.g 1_Firstfile.sql will have an output file 1_Firstfile.out.

In OSQL or SQLCMD you can set the error level threshold using the –V flag, see Books Online for more information. Unfortunately starting SQLCMD for each file can be quite time consuming (it was worse with earlier versions of OSQL where a delay was inbuilt!!).

An alternative not mentioned so far is to use the :r OSQL/SQLCMD script syntax.

echo /* Start of script */ > combined.txt
for %i in (*.sql) do echo :r %i >> combined.txt
ren combined.txt combined.sql
SQLCMD -E -S "(local)" -i combined.sql –d Adventureworks

Although the tools for building databases have moved on considerably since I first used this method, I still find occasions when it is a useful method to remember.

If you want to use Powershell to do this check out this post.

Filed under:

Comments

# Dew Drop - June 2, 2009 | Alvin Ashcraft's Morning Dew said on June 2, 2009 01:30 PM:

Pingback from  Dew Drop - June 2, 2009 | Alvin Ashcraft's Morning Dew

# Dew Drop - June 2, 2009 | Alvin Ashcraft's Morning Dew said on June 2, 2009 01:30 PM:

Pingback from  Dew Drop - June 2, 2009 | Alvin Ashcraft's Morning Dew

# Martin Bell UK SQL Server MVP said on October 31, 2011 10:26 PM:

One problem with building databases using batch files is the sys,sys_dependencies messages.

# sqlserver by alligin - Pearltrees said on January 8, 2012 12:47 PM:

Pingback from  sqlserver by alligin - Pearltrees

This Blog

SQL Blogs

Syndication