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.
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.

1 comment:

MR said...

I have one problem executing multiple scripts via SQLCMD as shown under this post. My scripts have many common-name variable declarations. So, this results in many errors. Is there a way to make a new query for each file to avoid this problem? I can't edit these files to have different variable names. Is BEGIN, END and Go statement a valid option?
Thank you.