如何使用批处理文件运行多个SQL脚本?

I have a case where i have got 10+ SQL script.

我有一个例子，我有10+ SQL脚本。

I don't want to go and run all my scripts 1 by 1.

我不想运行我所有的脚本1 * 1。

Is there a way that i can run all my scripts in succession in SQL Management studio.

我是否有办法在SQL Management studio中连续运行所有脚本?

I found this post. Creating a batch file seems easier.

我发现这篇文章。创建批处理文件似乎更容易。

This is all you need:

这就是你所需要的:

@echo off
ECHO %USERNAME% started the batch process at %TIME%  >output.txt


for %%f in (*.sql) do (
 (
sqlcmd.exe  -S servername -E   -d databasename -i %%f >>output.txt
)


pause

Replacing servername and databasename, but it seems to be not working.

替换servername和databasename，但它似乎不能工作。

Any ideas?

什么好主意吗?

7 个解决方案

#1

You can create a Strored Procedure to call all your Scripts. You could also create a schedule plan to run the scripts automaticaly.

您可以创建一个字符串过程来调用所有脚本。您还可以创建一个计划来自动运行脚本。

http://msdn.microsoft.com/en-us/library/aa174792(v=sql.80).aspx

http://msdn.microsoft.com/en-us/library/aa174792(v = sql.80). aspx

#2

You've got an unmatched parenthesis, there. Try

这里有一个不匹配的括号。试一试

for %%f in (*.sql) do sqlcmd.exe -S servername -E -d databasename -i %%f >>output.txt

对于(*.sql)中的%f执行sqlcmd。exe -S servername -E -d databasename - %f >>output.txt

I just saved it in a .cmd file and it appears to be working.

我刚把它保存在一个。cmd文件中，它似乎在工作。

#3

Yes, it's possible. You can do it with :r command of SQLCMD.

是的,这是可能的。可以使用SQLCMD的:r命令。

I strongly recommend you to read this article and do it with SQLCMD

我强烈建议您阅读本文并使用SQLCMD进行处理

http://www.mssqltips.com/sqlservertip/1543/using-sqlcmd-to-execute-multiple-sql-server-scripts/

#4

Here is an open source utility with source code http://scriptzrunner.codeplex.com/

这是一个开源实用程序，源代码http://scriptzrunner.codeplex.com/

This utility was written in c# and allows you to drag and drop many sql files and start running them against a database.

这个实用程序是用c#编写的，它允许您拖放许多sql文件并开始在数据库上运行它们。

#5

You probably already know this, in case you don't, the script you posted will only run on machines that have SQL server installed.

您可能已经知道了这一点，如果您不知道，那么您发布的脚本将只在安装了SQL server的机器上运行。

#6

You can use Batch Compiler add-in for SMSS, it let's you run multiple scripts at once, create SQLCMD scripts or consolidate them into a *.sql file.

您可以为SMSS使用批编译器外接程序，它让您同时运行多个脚本，创建SQLCMD脚本或将它们合并到*中。sql文件。

#7

Some batch trick

一些批处理技巧

cd %~dp0 //use this if you use 'for xxx in', it solved most of my problems 

ECHO %USERNAME% started the batch process at %TIME%  >output.txt


for %%f in (*.sql) do (
(
sqlcmd.exe  -S servername -E -d databasename -i %%f >>output.txt
)
echo %errorlevel%
pause

#1