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.
C:\>SQLCMD -i"c:\scripts\DropTables.sql"
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:
SQLCMD /i"c:\scripts\DropTables.sql" SQLCMD /i"c:\scripts\CreateTables.sql" SQLCMD /i"c:\scripts\CreateStoredProcedures.sql" PAUSE
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.
SQLCMD /i"c:\scripts\DropTables.sql" /b IF %ERRORLEVEL% > ( PAUSE ) SQLCMD /i"c:\scripts\CreateTables.sql" /b IF %ERRORLEVEL% > 0 ( PAUSE ) SQLCMD /i"c:\scripts\CreateStoredProcedures.sql" /b IF %ERRORLEVEL% > 0 ( PAUSE )
Reference: http://msdn.microsoft.com/en-us/library/ms162773.aspx