Method for SQL server to execute multiple scripts in sequence (using method of sqlcmd utility)

Time:2022-5-1

resolvent:

There are several ways to deal with this situation:

1. Purchase third-party software (it is generally estimated that few people buy it)

2. Program a small software to execute, but the logic requirement is relatively high, and the programming ability must have a certain level. I don’t have this for the time being.

3. Using the method introduced in this article, as for what it is, then look at:

Executing multiple scripts on SQL server using sqlcmd

Sqlcmd: using the sqlcmd utility, you can enter transact SQL statements, system procedures and script files at the command prompt, in the query editor in sqlcmd mode, in the windows script file, or in the operating system (CMD. Exe) job step of SQL server agent job. This utility uses ODBC to perform transact SQL batch processing. (from MSDN) the detailed syntax can be found on the Internet and will not be 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 files referenced by this file into the calling script. This will tell you to stop the current single query. And readjust the query to put the associated query in the appropriate position. In addition, use 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 go terminators. 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 called create_ DB. SQL, which is used to create a database called testdb. This script contains four other scripts (using the: R command), Used to generate other tables, table inserts, index creation, and the creation of stored procedures. One The. Bat file is used to create and execute sqlcmd commands.

 
Step 1: first create a folder under drive C: C: \ scripts. Then save the script in this folder:
Script 1: create_ DB. sql

Copy codeThe code is as follows:
/* SCRIPT: CREATE_DB.sql */
/*Create 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’
GO

Script 2: create_ INDEXES. sql

Copy codeThe code is as follows:
/*Create index*/
Print ‘start index creation’
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 stored procedure*/
Print ‘creating stored procedure’
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 data table*/
Print ‘creating data table’
GO
USE TestDB
GO
IF OBJECT_ID(‘EMPLOYEE’) 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 table employee’
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 in the root directory of drive C_ db. Bat to execute sqlcmd:

Double click the file to see:
Before execution, there is no testdb:

 

In execution:

 

After execution, all the things to be created are created:

 

 

Since the order of execution has been defined in script 1, it can be executed directly and successfully.

Summary:

According to personal experience, it is better to develop a batch execution tool. This method can be used when there are a few scripts.