TransactSQL to run another TransactSQL script
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).
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
Run Transact-SQL Script Files Using sqlcmd, Learn how to use sqlcmd to run a Transact-SQL script file. It can contain Transact -SQL statements, sqlcmd commands, and scripting variables. Use Transact-SQL Editor to Edit and Execute Scripts. 02/09/2017; 2 minutes to read +1; In this article. The Transact-SQL Editor provides you with a rich editing and debugging experience when you are working with scripts. It is invoked when you use the View Code contextual menu to open a database entity in a connected database or a project.
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)
EXECUTE (Transact-SQL), The change in execution context remains in effect until one of the following occurs: Another EXECUTE AS statement is run. A REVERT statement� 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?
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.
EXECUTE AS (Transact-SQL), Usually when we need to run a SQL script we open it in SQL Server scripts on a database and need to send the results to another person,� How to create, configure and drop a SQL Server linked server using Transact-SQL July 5, 2017 by Marko Zivkovic Linked servers allow getting data from a different SQL Server instance using single T-SQL statement.
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
Execute SQL Server Script Files with the sqlcmd Utility, For a simple scenario, let's say you have a different script for each database object that you create, one script for creating tables, another for creating views,� I have a problem with my Transact SQL script, I made a procedure in Transact SQL which has to execute a powershell script with a function. When I execute the file and method in the Command Prompt it
Assuming you want to keep the 10 scripts in their own individual files, I would say the easiest way to do what you want would be to create a batch file that executes osql.exe to execute the 10 scripts in the order you want.
Tips and Tricks - Executing a Batch of SQL Scripts, The sqlcmd utility is a command line tool that lets you run T-SQL on in SSMS ( SQL Server Management Studio) to run sqlcmd scripts due to a number Another way to start the sqlcmd utility is to right-click the Windows icon� I have to run this query from inside the python script using pyodbc (python 3): DBCC CHECKDB ('database_name') WITH ESTIMATEONLY In code I have the following: query = ''' DBCC
Basics of Running T-SQL Statements from Command Line using , Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $qcd update one value, remove another value and finally select the entire data set While we can see that we are able to run T-SQL directly in the script,�
Working with PowerShell's Invoke-SqlCmd, The :CONNECT keyword is used to connect to a different server using T-SQL, sqlcmd and Windows scripting are all integrated, and run using�
Overview of the SQLCMD utility in SQL Server, In order to minimize the security risk of executing malicious code outside SQL Server 2005, xp_cmdshell is disabled by default, and the following�
- when you say script do you mean a .sql file?
- yes, I do mean a .sql file. thanks!
- what does the
:rdo? Where is it documented? Could you provide a link?
- Note: Using :r in SSMS requires turning on SQLCMD mode: Query menu -> SQLCMD Mode.
- @Oded, give this a read: msdn.microsoft.com/en-us/library/ms174187.aspx
:rimports and executes a file.
- This 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.
"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
'. Same as you should do in command line) everything worked. SSMS 2012.
- This is nice; but just to add the caution that it'll only work for files whose contents fit inside that "varchar(MAX)".
- SINGLE_CLOB worked for me, SINGLE_BLOB gave me a syntax error
- Best to use SQLCMD with SQL 2005 and up. Supports all the OSQL (and ISQL) features, along with some new wifty features an add-ins.
- @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.
- Ever since I hit a hidden yet fundamental difference between isql and osql, I check them out when a new version gets rolled out