SQL TransactSQL 运行另一个 TransactSQL 脚本

TransactSQL to run another TransactSQL script

提问by Steve Stedman

I have 10 transact SQL scripts that each create a table and fill it with data.

我有 10 个事务 SQL 脚本，每个脚本创建一个表并用数据填充它。

I am attempting to create 1 master sql script that will run each of the 10 other scripts.

我正在尝试创建 1 个主 sql 脚本，该脚本将运行其他 10 个脚本中的每一个。

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

有没有办法使用 TSQL/TRANSACTSQL for Microsoft SQL Server 2008 从当前 tsql 脚本中执行另一个 tsql 脚本？

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

这旨在通过 SQL Server Management Studio (SSMS) 运行。

Thanks!

谢谢！

回答by Abe Miessler

Try this if you are trying to execute a .sql file in SSMS:

如果您尝试在 SSMS 中执行 .sql 文件，请尝试以下操作：

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

注意：要运行此命令，请打开 sql 命令模式（查询 > SQLCMD 模式）

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 （我认为更常见）来做到这一点：

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

回答by Pesche Helfer

Or just use openrowset to read your script into a variable and execute it:

或者只是使用 openrowset 将您的脚本读入一个变量并执行它：

DECLARE @SQL varchar(MAX)
SELECT @SQL = BulkColumn
FROM OPENROWSET
    (   BULK 'MeinPfad\MeinSkript.sql'
    ,   SINGLE_BLOB ) AS MYTABLE

--PRINT @sql
EXEC (@sql)

回答by Philip Kelley

The simplest way would be to make your scripts stored procedures, and to call (via the EXECUTEcommand) 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).

最简单的方法是让您的脚本存储过程，并EXECUTE从中央过程依次调用（通过命令）每个过程。如果您要一遍又一遍地运行完全相同的脚本（或传入不同参数的相同脚本），这是理想的选择。

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.

如果您的脚本是 .sql（或任何类型的文本）文件，正如@Abe Miesller 所说（已投票），当启用 SQLCMD 模式时，您可以通过 :r 命令在 SSMS 中运行它们。您必须知道并编写脚本的确切文件路径和名称。这不能在存储过程中完成。

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.

最后一个替代方案，可用于“已知”文件名并且对于任意文件名（例如，当前加载在子文件夹中的所有文件）是必需的，是利用扩展过程的力量XP_CMDSHELL。这样的解决方案可以非常快地获得 compelx（使用它来检索文件列表，通过 xp_cmdshell 构建和执行一个字符串，依次为每个文件调用 SQLCMD，通过输出文件管理结果和错误，它一直持续下去）所以我只作为最后的手段这样做。

回答by Shane Delmore

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.

您可以使用 osql 或更好但更新的 sqlcmd 几乎可以互换使用。我在这个例子中使用 osql 只是因为我碰巧有一个代码示例，但在生产中我使用的是 sqlcmd。这是我用来针对数据库运行更新脚本的较大过程中的代码片段。它们按主要、次要、发布、构建排序，因为我使用该约定来命名我的脚本以跟踪发布。您显然错过了我所有的错误处理、我从数据库中提取可用脚本的部分、设置变量等，但您可能仍然会发现此代码段很有用。

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.

我喜欢使用 osql 或 sqlcmd 的主要部分是您可以在 ssms 或存储过程（可能按计划调用）或批处理文件中运行此代码。非常灵活。

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

回答by Russell McClure

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.

假设您想将 10 个脚本保留在它们自己的单独文件中，我会说最简单的方法是创建一个批处理文件，该文件执行 osql.exe 以按您想要的顺序执行 10 个脚本。

回答by phil_w

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

我发现用路径定义一个变量很有用，如果我想执行一组脚本，比如运行一个测试，比如： :setvar path "C:\code\branch-qa" :r $(path)\tables\client.sql :r $(path)\tables\item.sql :r $(path)\proc\clientreport.sql exec clientreport