是否可以从 SQL 查询执行文本文件？

Is it possible to execute a text file from SQL query?

提问by ChrisHDog

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 文件。我想从查询中的 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 并在管理工作室中运行查询。

回答by Gulzar Nazim

use xp_cmdshelland sqlcmd

使用xp_cmdshell和sqlcmd

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

回答by Archi Moore

Very helpful thanks, see also this link: Execute SQL Server scriptsfor a similar example. To turn xp_cmdshellon and off see below:

非常有帮助，谢谢，另请参阅此链接： 执行 SQL Server 脚本以获得类似示例。要打开xp_cmdshell和关闭，请参见下文：

On

在

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 

回答by Pesche Helfer

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)

回答by Bruce Thompson

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  

回答by Mitch Wheat

I wouldn't recommended doing this, but if you really have to then the extended stored procedure xp_cmdshellis 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 在后台运行命令，因此，它不能用于运行需要用户输入的程序。

回答by John Dyer

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.

在堆栈溢出时快速搜索“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 类运行它。

回答by Alper Ebicoglu

Open windows command line (CMD)

打开 windows 命令行 (CMD)

sqlcmd -S localhost -d NorthWind -i "C:\MyScript.sql"

回答by live-love

For Windows Authentication, if you are running as another user: Open Command Prompt as your Windows user (Right click on it, Open File Location, Shift + Right Click, Run as a different user)

对于 Windows 身份验证，如果您以其他用户身份运行：以您的 Windows 用户身份打开命令提示符（右键单击它，打开文件位置，Shift + 右键单击​​，以其他用户身份运行）

 sqlcmd -S localhost\SQLEXPRESS -d DatabaseName-i "c:\temp\script.sql"

Or if you are using Sql Server user:

或者，如果您使用的是 Sql Server 用户：

sqlcmd -S localhost\SQLEXPRESS -d DatabaseName-i "c:\temp\script.sql" -U UserName -P Password

Replace localhost\SQLEXPRESS with you server name if not local server.

如果不是本地服务器，请将 localhost\SQLEXPRESS 替换为您的服务器名称。

回答by Adam Henderson

For anybody stumbling onto this question like I did and might find this useful, I liked Bruce Thompson's answer(which ran SQL from files in a loop), but I preferred Pesche Helfer's approach to file execution(as it avoided using xp_cmdshell).

对于任何像我一样绊倒这个问题并且可能会发现这很有用的人，我喜欢Bruce Thompson 的答案（它在循环中从文件中运行 SQL），但我更喜欢Pesche Helfer 的文件执行方法（因为它避免使用 xp_cmdshell）。

So I combined the two (and tweaked it slightly so it runs everything from a folder instead of a manually created list):

所以我将两者结合起来（并稍微调整了一下，以便它从文件夹而不是手动创建的列表中运行所有内容）：

DECLARE @Dir NVARCHAR(512) = 'd:\SQLScriptsDirectory'

DECLARE @FileList TABLE (
  subdirectory NVARCHAR(512),
  depth int,
  [file] bit
)

INSERT @FileList
EXEC Master.dbo.xp_DirTree @Dir,1,1

WHILE (SELECT COUNT(*) FROM @FileList) > 0  
BEGIN  
   DECLARE @FileName NVARCHAR(MAX) = (SELECT TOP(1) subdirectory FROM @FileList) 
   DECLARE @FullPath NVARCHAR(MAX) = @Dir + '\' + @FileName

   DECLARE @SQL NVARCHAR(MAX)
   DECLARE @SQL_TO_EXEC NVARCHAR(MAX)
   SELECT @SQL_TO_EXEC = 'select @SQL = BulkColumn
   FROM OPENROWSET
       (   BULK ''' + @FullPath + '''
       ,   SINGLE_BLOB ) AS MYTABLE'

   DECLARE @parmsdeclare NVARCHAR(4000) = '@SQL varchar(max) OUTPUT'  

   EXEC sp_executesql @stmt = @SQL_TO_EXEC
                 , @params = @parmsdeclare
                 , @SQL = @SQL OUTPUT  

   EXEC (@sql)
   DELETE FROM @FileList WHERE subdirectory = @FileName  

   PRINT 'EXECUTED: ' + @FileName     
END