Source in External Files in a T-SQL Script

There are many times when we need to do a build of a database. For these occasions I find T-SQL scripts and using sqlcmd to be the ticket.

Using sqlcmd to Create Tables and Other SQL Artifacts

Doing such a build requires building the tables, the indexes, the foreign key contraints, stored procedures, etc... etc...

I prefer to have these various files as seperate source code for a variety of reasons. For example, when building certain test builds or development builds it is easier to load the data directly after building the tables. It takes a lot less time to load the data (depending on the volume of data) before the indexes have been built. There are certainly other reason I have, but this one should suffice.

The Script

As you can see in the script below by using the :r construct I can reference external files. This allows me to keep my source code in seperate files but collate them together into a script to perform a build.
The script below is in a file named bld_TNGMGR.sql.


:On Error exit

PRINT 'CREATING tngmgr Tables'
:r c:\ss2008s\tngmgr\Index_Def.sql
PRINT 'CREATING tngmgr Indexes'
:r c:\ss2008s\tngmgr\Index_Def.sql
PRINT 'CREATING tngmgr FK Constraints'
:r c:\ss2008s\tngmgr\FKey_Def.sql

It should be noted that the order that I perform the build above is important. For instance, I cannot add the indexes or the foreign key contraints until the tables have been built. I am assuming that you are already aware of these matters of precedence.

You can down load the three referenced scripts here.

Running the Script

>sqlcmd -i c:\ss2008s\tngmgr\bld_TNGMGR.sql

The -i argument is the input switch. What follows the -i switch is the reference to the file I wish to run.