tools

Usually, when we run SQL scripts, it is done on an ad hoc basis.  We usually run one file at a time just once.  From time to time there is the need to run a set of SQL script files.  One common reason I’ve seen for doing this is to rebuild a database for testing.  If you had all of your create tables/stored procedure commands stored in a series of SQL script files, then you could rebuild your database fairly easily by executing the SQL files in sequence.

 
SQL Server provides a tool to do this called SQLCMD.

The sqlcmd utility lets you run entire SQL script files full of t-sql via command prompt.  Leveraging this tool, you can create a batch file to run the sql script files in sequence.

 
 
How To Execute A SQL Script File Via Command Prompt With SQLCMD

Although there are TONS of command line options for executing a sql script.  Just executing a sql script file with the basic options is very easy.

The above command uses the -i option to specify the input file of DropTables.sql.  Everything inside this file will be execute on the current database.

 
NOTE: When you create your SQL script files, I always recommend putting a USE statement at the top of your script.  This will ensure that you are using the correct database when your dropping and creating tables.

 
 
Run A Batch Of SQL Script Files

Now that you know how to run one SQL script file… running multiple should be easy.  All you need to do is create a batch file and run one script after the other.

 
Your batch file will look something like this:

 
 
What About Errors?

So what happens if the SQL script file throws an error?  The above commands I showed you will just ignore any errors.  If you want to capture your errors, you need to add the command line option /b.  This flag will tell the sqlcmd utility to stop processing the script and output a DOS ERRORLEVEL value.  You can then check the DOS ERRORLEVEL value after each execution.

 
 
Reference: http://msdn.microsoft.com/en-us/library/ms162773.aspx