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.
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.
SET NOCOUNT ON GO :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.