批量执行SQL文件

梦里梦外; 2022-05-15 05:05 386阅读 0赞

摘要：很多时候我们在做系统升级时需要将大量的.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用处远远不止这些，感兴趣的朋友可以自己研究一下。

[osql1]: /images/20220515/acca126ea1f143678f4261d427748562.png
[osql2]: /images/20220515/b625cf82a6bd4ac6a67f93aed2ca5048.png
[osql3]: /images/20220515/d7e215ea4f964845b16f1762ab99fce6.png