batch execution of SQL files

Original: Batch Execute SQL file

Absrtact: Many times when we are doing a system upgrade we need to execute a large number of. sql files, which is very inconvenient. And given the order of execution and the ease of operation of the customer service, can you find an easy way to execute these SQL files in bulk?

Main content:

1. Preparatory work
2. batch execution of SQL files with the Osql/ocmd command
3. Use Master: xp_cmdshell Stored Procedure Call Osql/ocmd command
4. Summarize

First, the preparatory work

Since we are going to execute the SQL file in bulk, we will first build two SQL files: 1.sql and 2.sql.

1.sql of content:

 INSERT into VALUES'  1.sql_ ' + CAST (GETDATEasNVARCHAR(Ten )) )

Content in 2.sql:

 INSERT into VALUES'  2.sql_ ' + CAST (GETDATEasNVARCHAR(Ten )) )

ii. batch execution of SQL files with Osql/ocmd batches

If we need to execute SQL at the command line, we need to use the osql command, which, of course, is a tool that comes with SQL Server, with specific uses such as:

Note: In the we can see that osql does not support all the features of SQL Server 2008, and if you need to use all the features of SQL Server 2008, you can use the Ocmd command.

With the Osql/ocmd command to batch execute a SQL file, of course, you need to write a batch command, for example, we execute the above SQL file to write the following statement in 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 "

Then save to Sql.bat with the above 1.sql and 2.sql placed under the same file, in the execution of double-click Sql.bat on it.

Is the result before and after operation:

third, use master. xp_cmdshell stored procedure call osql/ocmd command

We use batch processing to do the SQL file batch execution, then we in the program to do, of course, can call batch file, but we still prefer to use SQL to complete, we have to mention Master. xp_cmdshell This stored procedure. Master.. xp_cmdshell This stored procedure allows us to invoke the command shell in the form of an SQL statement, so of course we can basically use master in cmd . xp_cmdshell can do it.

.. xp_cmdshell Before we had to configure the server securely, we would throw the following error if we did not configure SQL Server:

sql Server blocking the component xp_cmdshell ' The process of Sys.xp_cmdshell ' sp_configure enable xp_cmdshell ' xp_cmdshell ' sql Server Books Online " outer application Configurator "

There are many ways to configure it, and here we are using SQL for Security Configuration (note that for security purposes, after you call Master: Remember to close after xp_cmdshell), here is how we modify the security configuration and use Master. xp_cmdshell SQL statement to execute the osql/ocmd command:

--Open Configuration
EXECsys.sp_configure'Show advanced Options',1
GO
--to install
RECONFIGURE
GO
--Configure xp_cmdshell to be available
EXECsys.sp_configure'xp_cmdshell',1
GO
--to install
RECONFIGURE
GO
--Execute xp_cmdshell Command
EXECMaster.. xp_cmdshell'osql-u sa-p sa-d northwind-i F:\CSharp\TimingJobs\1.sql'
EXECMaster.. xp_cmdshell'osql-u sa-p sa-d northwind-i F:\CSharp\TimingJobs\2.sql'
GO

--Open Configuration
EXECsys.sp_configure'Show advanced Options',1
GO
--to install
RECONFIGURE
GO
--configuration xp_cmdshell is not available
EXECsys.sp_configure'xp_cmdshell',0
GO
--to install
RECONFIGURE
GO

Iv. Summary

Today we mainly talked about how to use Osql/ocmd in command line and SQL (using master: xp_cmdshell) batch execution of SQL files, in fact, osql/ocmd use far more than these, interested friends can do their own research.

This work is licensed under the Creative Commons Attribution 2.5 China license agreement, and is welcome to be reproduced, interpreted or used for commercial purposes. But reprint please indicate from Tri Jiangtao (KENSHINCUI), and contain related link.

batch execution of SQL files