是否可以执行SQL查询中的文本文件?

I have a number of generated .sql files that I want to run in succession. I'd like to run them from a SQL statement in a query (i.e. Query Analyzer/Server Management Studio).
Is it possible to do something like this and if so what is the syntax for doing this?

我有许多生成的.sql文件，我想要连续运行它们。我希望在查询(例如，query Analyzer/Server Management Studio)中的SQL语句中运行它们。有可能做这样的事情吗?如果有可能的话，做这个的语法是什么?

I'm hoping for something like:

我希望的是:

exec 'c:\temp\file01.sql' 
exec 'c:\temp\file02.sql'

I am using SQL Server 2005 and running queries in management studio.

我正在使用SQL Server 2005，并在management studio中运行查询。

6 个解决方案

#1

use xp_cmdshell and sqlcmd

使用xp_cmdshell,sqlcmd

EXEC xp_cmdshell  'sqlcmd -S ' + @DBServerName + ' -d  ' + @DBName + ' -i ' + @FilePathName

#2

Very helpful thanks, see also this link: Execute SQL Server scripts for a similar example. To turn xp_cmdshell on and off see below:

非常有用的感谢，请参见这个链接:为类似的示例执行SQL Server脚本。打开和关闭xp_cmdshell请参见:

在

SET NOCOUNT ON  
EXEC master.dbo.sp_configure 'show advanced options', 1 
RECONFIGURE 
EXEC master.dbo.sp_configure 'xp_cmdshell', 1 
RECONFIGURE

Off

从

EXEC master.dbo.sp_configure 'xp_cmdshell', 0 
RECONFIGURE 
EXEC master.dbo.sp_configure 'show advanced options', 0 
RECONFIGURE  
SET NOCOUNT OFF

#3

This is what I use. Works well and is simple to reuse. It can be changed to read all files in the directory, but this way I get to control which ones to execute.

这就是我用的。工作良好，易于重用。可以更改为读取目录中的所有文件，但是通过这种方式，我可以控制要执行哪些文件。

/*  
execute a list of .sql files against the server and DB specified  
*/  
SET NOCOUNT ON  

SET XACT_ABORT ON  
BEGIN TRAN  

DECLARE @DBServerName   VARCHAR(100) = 'servername'  
DECLARE @DBName VARCHAR(100) = 'db name'  
DECLARE @FilePath   VARCHAR(200) = 'path to scrips\'  
/*

create a holder for all filenames to be executed  

*/  
DECLARE @FileList TABLE (Files NVARCHAR(MAX))  

INSERT INTO @FileList VALUES ('script 1.sql')  
INSERT INTO @FileList VALUES ('script 2.sql')  
INSERT INTO @FileList VALUES ('script X.sql')  

WHILE (SELECT COUNT(Files) FROM @FileList) > 0  
BEGIN  
   /*  
   execute each file one at a time  
   */  
   DECLARE @FileName NVARCHAR(MAX) = (SELECT TOP(1) Files FROM @FileList)  
   DECLARE @command  VARCHAR(500)  = 'sqlcmd -S ' + @DBServerName + ' -d  ' + @DBName + ' -i "' + @FilePath + @Filename +'"'  
   EXEC xp_cmdshell  @command   

   PRINT 'EXECUTED: ' + @FileName     
   DELETE FROM @FileList WHERE Files = @FileName  
END  
COMMIT TRAN

#4

I wouldn't recommended doing this, but if you really have to then the extended stored procedure xp_cmdshell is what you want. You will have to first read the contents of the file into a variable and then use something like this:

我不建议这样做，但是如果必须这样做，那么扩展存储过程xp_cmdshell就是您想要的。您必须首先将文件的内容读入一个变量，然后使用如下内容:

DECLARE @cmd sysname, @var sysname
SET @var = 'Hello world'
SET @cmd = 'echo ' + @var + ' > var_out.txt'
EXEC master..xp_cmdshell @cmd

Note: xp_cmdshell runs commands in the background, because of this, it must not be used to run programs that require user input.

注意:xp_cmdshell在后台运行命令，因此，它不能用于运行需要用户输入的程序。

#5

Or just use openrowset to read your script into a variable and execute it (sorry for reviving an 8 years old topic):

或者使用openrowset将脚本读入一个变量并执行它(不好意思，这个主题已经有8年的历史了):

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

--PRINT @sql
EXEC (@sql)

#6

Take a look at OSQL. This utility lets you run SQL from the command prompt. It's easy to get installed on a system, I think it comes with the free SQL Server Express.

看看OSQL。这个工具允许您从命令提示符运行SQL。安装在系统上很容易，我认为它附带了免费的SQL Server Express。

Using the osql Utility

使用osql实用工具

A qick search of "OSQL" on stack overflow shows a lot of stuff is available.

在stack overflow上搜索“OSQL”，你会发现很多东西都是可用的。

The main thing to handle properly is the user and password account parameters that get passed in on the command line. I have seen batch files that use NT file access permissions to control the file with the password and then using this file's contents to get the script started. You could also write a quick C# or VB program to run it using the Process class.

正确处理的主要内容是在命令行中传入的用户和密码帐户参数。我见过一些批处理文件，它们使用NT文件访问权限用密码控制文件，然后使用这个文件的内容启动脚本。您还可以编写一个快速c#或VB程序来使用Process类运行它。

#1