Author: David Eulerdate: 2004/09 / 28Email: de_euler-david@yahoo.com.cn
if you have any problem, please contact me:)
Sometimes we need to run all SQL scripts under a folder (possibly txt or SQL, etc.) to update Schema, when the file needs multiple execution, then multiple statements can be used to perform SQL scripts.
- The following query statement runs in SQL Query, will execute the specified SQL script under the D: / VSS, these scripts suffix are TXT
Exec master..xp_cmdshell 'isql -u sa -p 123 -i d: /vss/mschema.txt' exec master..xp_cmdshell 'isql -u sa -p 123 -i d: /vss/mschema_030311.txt' Exec Master .. xp_cmdshell 'isql -u sa -p 123 -i d: /vss/mschema_030416.txt'exec master..xp_cmdshell' isql -u sa -p 123 -i d: /vss/mschema_030417.txt'exec master .. Xp_cmdshell 'isql -u sa -p 123 -i d: /vss/mschema_030513.txt'exec master..xp_cmdshell' isql -u sa -p 123 -i d: /vss/mschema_030613.txt'exec master..xp_cmdshell ' ISQL -U SA -P 123 -i D: /VSS/MssChema_031010.txt'exec master..xp_cmdshell 'ISQL -U SA -P 123 -i D: /VSS/MssChema_050810.txt'
EXEC MASTER..XP_CMDSHEC 'ISQL -U SA -P 123 -i D: /VSS/TaxInvoice.SQL'
Of course, we can do not use the stored procedure, but just execute these commands in a batch file updateschema.cmd.
Here is the contents of UpdatesChema.cmd:
The following command will execute the specified SQL script under the D: / VSS:
ISQL -U SA -P 123 -i D: /VSS/Msschema.txt isql -u sa -p 123 -i d: /vss/mschema_030311.txt isql -u sa -p 123 -i d: / vss / msschema_030416. TXTISQL -U SA -P 123 -i D: /VSS/Msschema_030417.txtisql -u sa -p 123 -i d: /vss/mschema_030513.txtisql -u sa -p 123 -i d: /vss/mschema_030613.txtisql - U SA -P 123 -i D: /VSS/MssChema_031010.txtisql -u sa -p 123 -i d: /vss/mschema_050810.txt
Note:
1.Exec statement is used to perform a stored procedure, XP_cmdshell is an extension stored procedure in the Master database, which can be used to execute the string of the system command.
For example, exec master..xp_cmdshell 'dir * .exe' View all EXE files in the current directory. 2.ISQL is a utility of SQL Server, you can understand an external program, if SQL uses the default installation,
You can find this isql.exe file in the C: / Program Files / Microsoft SQL Server / 80 / Tools / Binn directory.
ISQL can be used to perform Transact-SQL statements, stored procedures, and script files,
Parameters-Used to specify the username of the login server,
-P to specify a password,
Parameter -i is used to specify the script file that reads the execution.
The parameter -o is used to specify the file name that saves the output result.
3. Other Oracle, DB2, MySQL, PostgreSQL, etc., there are also corresponding tools that also have similar functions.
