SQL Server executes multiple scripts in sequence (how to use the sqlcmd utility)

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: The sqlcmd utility can be used to query the editor at a command prompt, in SQLCMD mode, in a Windows Script file, or in the operating system (Cmd.exe) of the SQL Server proxy job) in the 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

Copy codeThe Code is as follows:
/* SCRIPT: CREATE_DB. SQL */
/* Create a TestDB database */

-- This is the main caller for each script
SET NOCOUNT ON
GO

PRINT 'start creating TestDB database'
If exists (SELECT 1 from sys. databases where name = 'testdb ')
Drop database TestDB
GO
Create database TestDB
GO

: 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. SQL

PRINT 'created successfully'
GO

Script 2: CREATE_INDEXES. SQL

Copy codeThe Code is as follows:
/* Create an Index */
PRINT 'start creating Index'
GO
USE TestDB
GO
If not exists (SELECT 1
From sys. INDEXES
Where name = 'ix _ EMPLOYEE_LASTNAME ')
Create index IX_EMPLOYEE_LASTNAME on dbo. EMPLOYEE (LASTNAME, FIRSTNAME)
GO
If 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

Copy codeThe Code is as follows:
/* Create a stored procedure */
PRINT 'Creating a stored Process'
GO
USE TestDB
GO
IF OBJECT_ID ('get _ EMPLOYEE_TIMECARDS ') IS NOT NULL
Drop procedure dbo. GET_EMPLOYEE_TIMECARDS
GO
Create procedure dbo. GET_EMPLOYEE_TIMECARDS @ EMPLOYEEID INT
AS
SET NOCOUNT ON

SELECT *
From dbo. EMPLOYEE E
Join dbo. timecard t on e. EMPLOYEEID = T. EMPLOYEEID
Where e. EMPLOYEEID = @ EMPLOYEEID
ORDER BY DATEWORKED

GO

Script 4: CREATE_TABLES. SQL

Copy codeThe Code is as follows:
/* Create a data table */
PRINT 'Creating a data table'
GO
USE TestDB
GO
IF OBJECT_ID ('employe') IS NOT NULL
Drop table dbo. EMPLOYEE
GO
Create table dbo. EMPLOYEE
(
Employeeid int identity (1, 1)
NOT NULL
Primary key,
Firstname varchar (50 ),
Lastname varchar (50)
)
GO

IF OBJECT_ID ('timecard ') IS NOT NULL
Drop table dbo. TIMECARD
GO
Create 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
)
GO

DECLARE @ TOTAL_TABLES INT
SET @ TOTAL_TABLES = 2

Script 5: TABLE_INSERTS. SQL

Copy codeThe Code is as follows:
/* Insert table data */

PRINT 'total tables created = '+ CAST (@ TOTAL_TABLES as varchar)
GO
PRINT 'inserting data into the table employe'
GO
USE TestDB
GO
Insert into dbo. EMPLOYEE
(FIRSTNAME, LASTNAME)
SELECT 'john ',
'Doe'
GO
Insert into dbo. EMPLOYEE
(FIRSTNAME, LASTNAME)
SELECT 'Jane ',
'Doe'
GO
Insert 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:

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.