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
Subscribe to:
Post Comments (Atom)
1 comment:
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.
Post a Comment