Executing an SQL script from an SQL script

May 9, 2008 at 9:12 AMAmer Gerzic

There are many challenges surrounding database development. With the project size, the development becomes more complicated and harder to maintain. One specific issue is the question of going about writing SQL script so that the maintenance and/or updates are handled properly. I was always found of "divide and conquer" technique, which I religiously follow during each and every project. In case of database development, I try to divide my script into multiple atomic entities, which I can execute independently or combined. The advantage: Updates/modifications are handled as independent as possible, affecting only necessary part of the project.

However, unlike other programming languages that I used, SQL scripts presented fundamental challenge (for my style of programming): How do I execute all scripts combined? Searching the web, I stumbled across different suggestions, but I was not very impressed. Granted, most of recommendations focused on keeping the solution compatible over multiple platforms. However, in my case, I knew that I will be using SQL Server 2005, so that my solution could be specific. Searching some more, I found SQLCMD mode. SQLCMD mode in SQL Server 2005 enables programmer to utilize additional commands (besides regular SQL statements). In my case, I was specifically interested in script execution, for which I needed to utilize command ":r". Well, let's look at following example:

-------------------------------------------------------------------------------
-- 
-- Author:  Amer Gerzic
--
-- Using SQLCMD mode, we are installing all SQL scripts.
-- To enable SQLCMD mode in MSSQL Server Management Studio Express select menu
-- "Query"->"SQLCMD Mode"
------------------------------------------------------------------------------- 

:connect server\SQLEXPRESS -U sa -P XXXXXX 

:r C:\code\db.sql
:r C:\code\table.sql 

:r C:\code\sp_1.sql
:r C:\code\sp_2.sql
:r C:\code\sp_3.sql
:r C:\code\sp_4.sql
:r C:\code\sp_5.sql 

GO

In this way, all scripts are executed in the order they appear. However, it is important that the script is executed in SQLCMD mode. To enable such mode in SQL Server Query Editor you must select following option using Query menu:

In addition, following link might be helpful:

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

Posted in: SQL Server

Tags:

Add comment

biuquote
Loading