TransactSQL to run another TransactSQL script

Question

I have 10 transactional SQL scripts, each of which creates a table and populates it with data.

I am trying to create 1 sql script wizard that will run each of 10 other scripts.

Is there a way with TSQL / TRANSACTSQL for Microsoft SQL Server 2008 to execute another tsql script from the current tsql script?

This is intended to be run through SQL Server Management Studio (SSMS).

Thank!

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

If these are scripts that you run quite often, you might consider deleting them in a saved proc and running them that way ...

You can also do this via sqlcmd (which I find more common):

 sqlcmd -S serverName\instanceName -i C:\Scripts\Script1.sql 

Answer 2

The easiest way is to make your scripts stored procedures and call (via the EXECUTE command) each procedure, in turn, from the central procedure. This is ideal if you run the same script (s) (or the same script with different parameters) over and over again.

If your scripts are a .sql file (or any text file), as @Abe Miesller says (upvoted), you can run them from SSMS using the: r command when SQLCMD mode is enabled. You must know and the script the exact path and file name. This cannot be done from a stored procedure.

The last alternative, used with "known" file names and necessary for arbitrary file names (say, for all files loaded into the entire subfolder), is to use the power of the XP_CMDSHELL extended procedure. Such solutions can get compelx pretty quickly (use it to extract a list of files, build and execute through xp_cmdshell a line that calls SQLCMD for each file, in turn, managing results and errors with the help of output files, it goes on and on), so I would just do it as a last resort.

Answer 3

You can use osql or better, but the new sqlcmd is almost interchangeable. I use osql in this example only because I had to have a sample code sitting, but in production I use sqlcmd. Here is a snippet of code from a larger procedure that I use to run update scripts for databases. They are ordered by major, minor, releases, build, as I call my scripts, using this agreement to track releases. Obviously, you miss all of my error handling, the parts where I get the available scripts from the database, variable settings, etc., but you can still find this snippet useful.

The main part that I like about using osql or sqlcmd is that you can run this code in ssms or in a stored procedure (possibly on a schedule) 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 

Answer 4

Assuming you want to save 10 scripts in your separate files, I would say that the easiest way to do what you want is to create a batch file that runs osql.exe to execute 10 scripts in the order in which you want to.

Answer 5

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)