Overview: As a DBA, it is often necessary to use the SQL script provided by the developer to update the official database. However, a reasonable development process is provided when a script is submitted to the DBA for execution, there may already be hundreds of SQL files and execution sequence. For example, the company I work for now has more than a dozen customers. Each customer has a single database, but the database structure and stored procedure
Overview: As a DBA, it is often necessary to use the SQL script provided by the developer to update the official database. However, a reasonable development process is provided when a script is submitted to the DBA for execution, there may already be hundreds of SQL files and execution sequence. For example, the company I work for now has more than a dozen customers. Each customer has a single database, but the database structure and stored procedure
Overview:
As a DBA, it is often necessary to use the SQL script provided by the developer to update the formal database. However, a reasonable development process is provided when the script is submitted to the DBA for execution, there may already be hundreds of SQL files and execution sequence, such as the company I work for, a dozen or more customers, each of which has a single database, however, the database structure, stored procedures, views, and so on are all the same. Each script (called an upgrade) is executed. If there are one hundred scripts, It will be executed thousands of times in order, this workload cannot be borne by one person.
Solution:
There are several methods to deal with this situation:
1. Buy third-party software (usually few people buy it)
2. I have to program a small software for execution, but this logic requirement is high and the programming capability must be at a certain level. I do not have this yet.
3. Use the method described in this article:
Use SQLCMD to execute multiple scripts on SQLServer:
SQLCMD: UseSqlcmdUtility, which can be used at the command prompt and in SQLCMD Mode"Query Editor"In the Windows Script file or in the SQL Server Agent job's operating system (Cmd.exe) job step, enter the Transact-SQL statement, system process, and script file. This utility uses ODBC to execute Transact-SQL batch processing. (From MSDN) Detailed syntax can be found on the Internet. It is not posted here.
SQLCMD has a very important command: r. Remember, SQLCMD is case sensitive. When: r finds that the SQL script is running, it will tell SQLCMD to put the file referenced by this file into the call script. This tells you to stop a single query. And re-adjust the query to put the associated query in the appropriate location. In addition, run the r command to execute multiple scripts in a batch so that you can define a separate variable set to include all scripts, but not the GO Terminator. SQLCMD has been introduced since 2005 and can be used to replace osql tools in the future. If you are not familiar with SQLCMD, you can think of it as a command line tool that can execute T-SQL commands and scripts from the operating system.
In the following example, create five SQL files associated with the TestDB database. The first script is CREATE_DB. SQL, which is used to create a database named TestDB. This script contains four other scripts (using the: r command .), Used to generate other tables, table insertion, index creation, and storage process creation. A. bat file is created to execute the SQLCMD command.
Step 1: first create a folder C: \ Scripts under drive C. Then store the script in this folder:
Script 1: CREATE_DB. SQL
/* SCRIPT: CREATE_DB. SQL * // * Create the TestDB database */-- This is the main caller for each scriptSET NOCOUNT ONGOPRINT 'to create the TestDB database' IF EXISTS (SELECT 1 FROM SYS. databases where name = 'testdb') drop database TestDBGOCREATE DATABASE TestDBGO: On Error exit: r c: \ Scripts \ CREATE_TABLES. SQL: r c: \ Scripts \ TABLE_INSERTS. SQL: r c: \ Scripts \ CREATE_INDEXES. SQL: r c: \ Scripts \ CREATE_PROCEDURES.sqlPRINT 'creation complete 'GO
Script 2: CREATE_INDEXES. SQL
/* Create Index */PRINT 'start to create index 'gouse TestDBGOIF not exists (SELECT 1 from sys. indexes where name = 'ix _ EMPLOYEE_LASTNAME ') create index IX_EMPLOYEE_LASTNAME on dbo. EMPLOYEE (LASTNAME, FIRSTNAME) goif not exists (SELECT 1 from sys. indexes where name = 'ix _ TIMECARD_EMPLOYEEID ') create index IX_TIMECARD_EMPLOYEEID on dbo. TIMECARD (EMPLOYEEID) GO
Script 3: CREATE_PROCEDURES. SQL
/* Create a stored PROCEDURE */PRINT 'IS creating the Stored PROCEDURE 'gouse TestDBGOIF OBJECT_ID ('get _ EMPLOYEE_TIMECARDS') is not null drop procedure dbo. GET_EMPLOYEE_TIMECARDSGOCREATE procedure dbo. GET_EMPLOYEE_TIMECARDS @ employeeid intas set nocount on select * from dbo. employee e join dbo. timecard t on e. EMPLOYEEID = T. employeeid where e. EMPLOYEEID = @ EMPLOYEEID ORDER BY DATEWORKEDGO
Script 4: CREATE_TABLES. SQL
/* Create a data TABLE */PRINT 'the data TABLE 'gouse TestDBGOIF OBJECT_ID ('employe') is not null drop table dbo. employeegocreate table dbo. EMPLOYEE (employeeid int identity (1, 1) not null primary key, firstname varchar (50), lastname varchar (50) GOIF OBJECT_ID ('timecard ') is not null drop table dbo. timecardgocreate table dbo. TIMECARD (timecardid int identity (1, 1) not null primary key, employeeid int not null, hoursworked tinyint not null, hourlyrate money not null, dateworked datetime not null) GODECLARE @ TOTAL_TABLES INTSET @ TOTAL_TABLES = 2
Script 5: TABLE_INSERTS. SQL
/* Insert table data */PRINT 'total tables created = '+ CAST (@ TOTAL_TABLES as varchar) GOPRINT' is inserting data INTO the table EMPLOYEE 'gouse TestDBGOINSERT into dbo. EMPLOYEE (FIRSTNAME, LASTNAME) SELECT 'john', 'doe 'goinsert into dbo. EMPLOYEE (FIRSTNAME, LASTNAME) SELECT 'jar', 'doe 'goinsert into dbo. EMPLOYEE (FIRSTNAME, LASTNAME) SELECT 'jeff ', 'doe' GO
Step 2: Create a bat file create_db.bat under the C root directory to execute SQLCMD:
SQLCMD -E -dmaster -ic:\Scripts\create_db.sqlPAUSE
Step 3: Execute the bat file directly under drive C:
Double-click the file and you will see:
Before execution, there is no TestDB:
Executing:
After execution, all the created items are created:
Because the execution sequence has been defined in script 1, you can directly execute it and the execution is successful.
Summary:
Based on personal experience, it is better to develop a batch execution tool. This method can be used when there are a few scripts.