Thursday, January 8, 2009

Executing multiple script files via SQLCMD

I'm sure most people either DBA or developers faced at least once a requirement to execute bunch of script files. As you know we can do it by using Script Task in SSIS, however I would like to share with you a infrequent used :r parameter in SQLCMD utilty. Let say you have three files (.sql) that do an UPDATE,INSERT and finally SELECT statements. All you need is to have a single batch file which will execute those .sql files.
Create a batch file named DML.sql and add the below code
:r "c:\myUpdate.sql"

:r "c:\myInsert.sql"

:r "c:\MySelect.sql"

Make sure that you leave free space between commands.
--Usage
EXEC master..xp_cmdshell 'SQLCMD -S Server\SQLSERVERDEV2005 -i"c:\DML.sql"'

I recommend you to read this article with more examples about how using :r parameter.
http://blogs.msdn.com/patrickgallucci/archive/2007/09/03/sqlcmd-and-the-power-of-the-little-r.aspx