批量执行SQL文件

Anonymity | | 访问(105)

  摘要:很多时候我们在做系统升级时需要将大量的.sql文件挨个执行,十分不方便。而且考虑到执行顺序和客服的操作方便性,能不能找到一种简单的方法来批量执行这些sql文件呢?

  主要内容:

  1.   准备工作

  2.   利用osql/ocmd命令批量执行sql文件

  3.   使用master..xp_cmdshell存储过程调用osql/ocmd命令

  4.   总结

  一、准备工作

既然是要批量执行sql文件,首先我们建立两个sql文件:1.sql和2.sql。

1.sql的内容:

INSERT INTO dbo.Products ( ProductName ) VALUES  ( '1.sql_'+CAST(GETDATE() AS NVARCHAR(10)) )

2.sql中的内容:

INSERT INTO dbo.Products ( ProductName ) VALUES  ( '2.sql_'+CAST(GETDATE() AS NVARCHAR(10)) )

  二、利用osql/ocmd批处理批量执行sql文件

我们如果需要在命令行执行sql,就需要用到osql命令,当然这个是sql server附带的工具,其具体用法如下图:

osql1

注意:在上图中我们可以看到osql 并不支持 SQL Server 2008的所有功能,如果需要使用SQL Server 2008的所有功能可以使用ocmd命令。

有了osql/ocmd命令要批量执行sql文件当然需要写一个批处理命令,例如我们执行上面sql文件就可以在txt中编写下面的语句:

osql -S"127.0.0.1"  -U"sa" -P"sa" -d"Northwind" -i"%CD%\1.sql"osql -S"127.0.0.1"  -U"sa" -P"sa" -d"Northwind" -i"%CD%\2.sql"

然后保存成sql.bat同上面的1.sql和2.sql放到同一个文件下,在执行时双击sql.bat就可以了。

下图是运行前后的结果:

osql2

osql3

  三、使用master..xp_cmdshell存储过程调用osql/ocmd命令

我们上面是使用批处理来进行sql文件的批量执行,那么我们在程序中如何来做呢,当然可以调用批处理文件了,但是我们更多时候还是喜欢使用sql来完成,此时就不得不提到master..xp_cmdshell这个存储过程。master..xp_cmdshell这个存储过程可以让我们以sql语句的方式调用command shell,那么当然我们在cmd中能做的基本上使用master..xp_cmdshell也都能做。

在使用master..xp_cmdshell之前我们必须要对服务器进行安全配置,如果我们不进行配置sql server就会抛出如下错误:

SQL Server 阻止了对组件'xp_cmdshell' 的过程'sys.xp_cmdshell' 的访问,因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用sp_configure 启用'xp_cmdshell'。有关启用'xp_cmdshell' 的详细信息,请参阅SQL Server 联机丛书中的"外围应用配置器"

配置的方式有多种,这里我们就通过sql来进行安全配置(注意为了安全起见在调用完master..xp_cmdshell之后记得关闭),下面是我们修改安全配置和使用master..xp_cmdshell执行osql/ocmd命令的sql语句:

 --打开配置
EXEC sys.sp_configure 'show advanced options',1
GO
--进行安装
RECONFIGURE
GO
--配置xp_cmdshell为可用
EXEC sys.sp_configure 'xp_cmdshell',1
GO
--进行安装
RECONFIGURE
GO
--执行xp_cmdshell命令
EXEC master..xp_cmdshell 'osql -U sa -P sa -d Northwind -i F:\CSharp\TimingJobs\1.sql'
EXEC master..xp_cmdshell 'osql -U sa -P sa -d Northwind -i F:\CSharp\TimingJobs\2.sql'
GO

--打开配置
EXEC sys.sp_configure 'show advanced options',1
GO
--进行安装
RECONFIGURE
GO
--配置xp_cmdshell为不可用
EXEC sys.sp_configure 'xp_cmdshell',0
GO
--进行安装
RECONFIGURE
GO

  四、总结

今天我们主要说了一下如何利用osql/ocmd在命令行及sql中(利用master..xp_cmdshell)批量执行sql文件,其实osql/ocmd用处远远不止这些,感兴趣的朋友可以自己研究一下。

  文章来源:http://www.cnblogs.com/kenshincui/archive/2011/03/01/1967870.html