How SQL Server executes multiple scripts sequentially (the sqlcmd utility uses the method) _mssql

Workaround:

There are several ways to deal with this situation:

1, the purchase of Third-party software (generally estimated that very few people buy)

2, their own programming a small software to carry out, but this logic requirements are relatively high, and programming ability to have a certain level, this I temporarily did not.

3, using the method introduced in this article, as to what is, then look:

execute multiple scripts on SQL Server using sqlcmd :

Sqlcmd: Use the sqlcmd utility to enter transact-s in a command prompt, in the Query Editor in SQLCMD mode, in a Windows script file, or in the operating system (Cmd.exe) job step of a SQL Server agent job QL statements, system procedures, and script files. This utility performs Transact-SQL batching with ODBC. (from MSDN) verbose syntax can be found on the web, not posted here.

Sqlcmd has a very important command:: R, remember that sqlcmd is case sensitive. When: R discovers that the SQL script is running, it tells Sqlcmd to put the file referenced by this file into the calling script. This will tell you to stop the current single query. and readjust the query to the appropriate location for the query that should be associated. In addition, use the: R command to execute multiple scripts in a batch so that you can define a separate set of variables that contain all the scripts, but do not include go terminators. The introduction of sqlcmd from 2005 can be used to replace osql tools in the future. If you are unfamiliar with sqlcmd, you can assume it is a command-line tool that performs T-SQL commands and scripts from the operating system.

In the following example, create 5 SQL files that have an associated effect on the TestDB database. The first script, called Create_db.sql, is used to create a database called TestDB. This script contains 4 other scripts (using: R command). To generate additional tables, table inserts, index creation, and stored procedure creation. A. bat file is used to create a command to execute sqlcmd.


The first step: Create a folder under C-disk: C:\Scripts. The script is then stored in this folder:
Script 1:create_db.sql

Copy Code code as follows:

* SCRIPT:CREATE_DB.sql * *
/* Create TESTDB Database * *

--This is the main caller for each script
SET NOCOUNT on
Go

PRINT ' starts 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 ' Create complete '
Go

Script 2:create_indexes.sql

Copy Code code as follows:

/* CREATE 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 Code code 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 Code code 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 is not NULL,
dateworked DATETIME not NULL
)
Go

DECLARE @TOTAL_TABLES INT
SET @TOTAL_TABLES = 2

Script 5:table_inserts.sql

Copy Code code 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 Two: Create a bat file Create_db.bat in the C-packing directory to perform sqlcmd:

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

In execution:

After execution, the created things are created:

Because the order of execution is already defined in script 1, execution can be done directly, and execution succeeds.

Summarize:

Based on personal experience, it is better to develop a batch execution tool, which can be used in a small number of scripts.