TransactSQL to run another TransactSQL script
Solution 1
Try this if you are trying to execute a .sql file in SSMS:
:r C:\Scripts\Script1.sql
:r C:\Scripts\Script2.sql
:r C:\Scripts\Script3.sql
...
note: for this to run turn on sql command mode (Query > SQLCMD Mode)
If these are scripts you run fairly often you might consider dropping them in a stored proc and running them that way...
You can also do it through sqlcmd (which I believe is more common):
sqlcmd -S serverName\instanceName -i C:\Scripts\Script1.sql
Solution 2
Or just use openrowset to read your script into a variable and execute it:
DECLARE @SQL varchar(MAX)
SELECT @SQL = BulkColumn
FROM OPENROWSET
( BULK 'MeinPfad\MeinSkript.sql'
, SINGLE_BLOB ) AS MYTABLE
--PRINT @sql
EXEC (@sql)
Solution 3
I find it useful to define a variable with the path, if I want to execute a set of scripts, say to run a test, something like:
:setvar path "C:\code\branch-qa"
:r $(path)\tables\client.sql
:r $(path)\tables\item.sql
:r $(path)\proc\clientreport.sql
exec clientreport
Solution 4
You can use osql or better yet the newer sqlcmd almost interchangeably. I am using osql in this example only because I happened to have a code sample sitting around but in production I am using sqlcmd. Here is a snipped of code out of a larger procedure I use to run update scripts against databases. They are ordered by major, minor, release, build as I name my scripts using that convention to track releases. You are obviously missing all of my error handing, the parts where I pull available scripts from the database, setup variables, etc but you may still find this snippet useful.
The main part I like about using osql or sqlcmd is that you can run this code in ssms, or in a stored procedure (called on a scheduled basis maybe) or from a batch file. Very flexible.
--Use cursor to run upgrade scripts
DECLARE OSQL_cursor CURSOR
READ_ONLY
FOR SELECT FileName
FROM #Scripts
ORDER BY Major, Minor, Release, Build
OPEN OSQL_cursor
FETCH NEXT FROM OSQL_cursor INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
IF ((@@fetch_status <> -2) AND (@result = 0))
BEGIN
SET @CommandString = 'osql -S ' + @@ServerName + ' -E -n -b -d ' + @DbName + ' -i "' + @Dir + @name + '"'
EXEC @result = master.dbo.xp_cmdshell @CommandString, NO_OUTPUT
IF (@result = 0)
BEGIN
SET @Seconds = DATEDIFF(s, @LastTime, GETDATE())
SET @Minutes = @Seconds / 60
SET @Seconds = @Seconds - (@Minutes * 60)
PRINT 'Successfully applied ' + @name + ' in ' + cast(@Minutes as varchar)
+ ' minutes ' + cast(@Seconds as varchar) + ' seconds.'
SET @LastTime = GETDATE()
END
ELSE
BEGIN
SET @errMessage = 'Error applying ' + @name + '! The database is in an unknown state and the schema may not match the version.'
SET @errMessage = @errMessage + char(13) + 'To find the error restore the database to version ' + @StartingVersion
SET @errMessage = @errMessage + ', set @UpToVersion = the last version successfully applied, then run ' + @name
SET @errMessage = @errMessage + ' manually in Query Analyzer.'
END
IF @name = (@UpToVersion + '.sql')
GOTO CleanUpCursor --Quit if the final script specified has been run.
END
FETCH ENDT FROM OSQL_cursor INTO @name
END
Solution 5
The simplest way would be to make your scripts stored procedures, and to call (via the EXECUTE
command) each procedure in turn from a central procedure. This is ideal if you're going to run the exact same script(s) over and over again (or the same script with different parameters passed in).
If your scripts are .sql (or any kind of text) file, as @Abe Miesller says (upvoted) you can run them from within SSMS via the :r command, when SQLCMD mode is enabled. You would have to know and script the exact file path and name. This cannot be done from within a stored procedure.
A last alternative, usable with "known" file names and necessary for arbitrary file names (say, all files currently loaded in a subfolder) is to leverage the power of extended procedure XP_CMDSHELL
. Such solutions can get compelx pretty fast (use it to retrieve list of files, build and execute via xp_cmdshell a string calling SQLCMD for each file in turn, manage results and errors via output files, it goes on and on) so I'd only do this as a last resort.
![Steve Stedman](https://i.stack.imgur.com/GU36P.png?s=256&g=1)
Steve Stedman
My name is STEVE STEDMAN, I live near Bellingham WA. I enjoy SQL Server performance tuning. follow me on twitter @SqlEmt.
Updated on July 05, 2022Comments
-
Steve Stedman about 2 years
I have 10 transact SQL scripts that each create a table and fill it with data.
I am attempting to create 1 master sql script that will run each of the 10 other scripts.
Is there a way with TSQL / TRANSACTSQL for Microsoft SQL Server 2008 to execute another tsql script from within the current tsql script?
This is intended to be run through the SQL Server Management Studio (SSMS).
Thanks!
-
Oded over 13 yearswhat does the
:r
do? Where is it documented? Could you provide a link? -
Joe Stefanelli over 13 yearsNote: Using :r in SSMS requires turning on SQLCMD mode: Query menu -> SQLCMD Mode.
-
Abe Miessler over 13 years@Oded, give this a read: msdn.microsoft.com/en-us/library/ms174187.aspx
:r
imports and executes a file. -
Philip Kelley over 13 yearsBest to use SQLCMD with SQL 2005 and up. Supports all the OSQL (and ISQL) features, along with some new wifty features an add-ins.
-
Russell McClure over 13 years@Philip Kelley: Thanks for the tip. I'm not sure how I missed that one. I guess since I don't do much from the command line.
-
Philip Kelley over 13 yearsEver since I hit a hidden yet fundamental difference between isql and osql, I check them out when a new version gets rolled out
-
VISQL almost 12 yearsThis rocks! I came across a big need to do this two days ago, but couldn't find a good example. Thanks! NOTE: Your filepath and file name should NOT have spaces in them for this to work. Also, I'm using the ":r" syntax, not sqlcmd.
-
VISQL almost 12 yearsWhen you use SQLCMD mode in SSMS 2008, Intellisense is disabled.
-
Renatas M. over 9 yearsUsed
:r
syntax. Got"Incorrect syntax was encountered while parsing :r
" error while using:r 'c:\some path\script.sql'
command. Others stated that path should not have spaces. But after changing to:r "c:\some path\script.sql"
(note path surrounded with"
not'
. Same as you should do in command line) everything worked. SSMS 2012. -
RobVious about 8 yearsWhat if one of the folders is a parameter - like
:r .\Folder\'$(EnvName)'.sql
- I can't get this to work -
Rob.Kachmar over 7 yearsThanks for the simple solution! @RobVious you should generate the variable declaration command and subsequent commands you want into a file and then execute the file. -- 1) Create your file of variables :OUT "C:\Scripts\SQLCMDVariableDeclarations.sql" PRINT ':SETVAR ScriptFullPath "C:\Scripts\Script Name with Spaces.sql"' GO :OUT stdout -- 2) Load your variables :r"C:\Scripts\SQLCMDVariableDeclarations.sql" -- 3) Use your variables :r $(ScriptFullPath) See: blogs.msdn.microsoft.com/sql_server_appendix_z/2013/02/23/…
-
Richardissimo about 6 yearsThis is nice; but just to add the caution that it'll only work for files whose contents fit inside that "varchar(MAX)".
-
AndrewD about 5 yearsSINGLE_CLOB worked for me, SINGLE_BLOB gave me a syntax error
-
Brian H over 4 yearsOne thing to note if you are tweaking one or more of your scripts and then testing them using this awesome :r solution (thank you Abe Miessler!!!). This just bit me but is obvious once you realize it or read this. If you change your script and don't save it, as we might normally do when not using the batch :r solution but rather are used to just pressing F5 to run it, the :r sqlcmd statement will run what is currently in the .sql file on disk and if you haven't saved what's in memory in your current SSMS session, you will think your tweak did nothing. Hope this helps someone.