TransactSQL to run another TransactSQL script

Question

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!

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

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 

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