Execute multiple scripts in SQL Server using sqlcmd

Original: Execute multiple scripts in SQL Server using sqlcmd

Overview:

As a dba, it is often necessary to update the official database with the SQL scripts provided by the developer, but a more reasonable development process may already have hundreds of SQL files when the script is submitted to the DBA for execution. And there are execution order, such as I now work in the company, more than 10 customers, each customer a library, but the database structure, stored procedures, views and so on are identical, each execution of the script (hereinafter referred to as the upgrade), if there are 100 scripts, then in order to execute thousands of times, this workload is not a person can bear.

Workaround:

There are several ways to deal with this situation:

1, purchase third-party software (generally estimated that few people buy)

2, self-programming a small software to execute, but this logic requirements are relatively high, and the ability to program a certain level, this I do not.

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

Use SQLCMD to execute multiple scripts on SQL Server:

SQLCMD:Use sqlcmd utility that can be used at the command prompt,SQLCMDmode of The Query Editor in, inWindowsIn the script file or in theSQL Serveroperating system for agent jobs(Cmd.exe)enter in the job stepTransact-SQLstatements, system procedures, and script files.  This utility usesODBCExecutionTransact-SQLbatch processing. (FromMSDNdetailed syntax can be found on the web, not posted here.

sqlcmd has a very important command:: R, remember thatsqlcmd is case-sensitive. When : R Discovery is running SQL script, it tells SQLCMD to put the file referenced in the file into the calling script. This will tell you to stop the current single query. and re-adjust the query, put the query should be associated in the appropriate location. In addition, using the : R command executes multiple scripts in a batch, allowing you to define a separate set of variables to contain all the scripts, but not the GO Terminator. the introduction of SQLCMD from 2005 can be used to replace the osql tool in the future . If you are unfamiliar 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 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 the: R command. ), which is used to generate additional tables, table inserts, index creation, and the creation of stored procedures. A. bat file is used to create a sqlcmd command to execute.

The first step: Create a folder under the C drive: C:\Scripts. Then store the script in this folder:

Script 1:create_db.sql

/* SCRIPT:CREATE_DB.sql *//* Create TestDB database */--This is the main caller for each scriptset NOCOUNT ' start creating Ongoprint Database ' IF EXISTS (SELECT 1 from SYS. DATABASES WHERE NAME = ' TestDB ') DROP database testdbgocreate database testdbgo:on Error exit:r C:\SCRIPTS\CREATE_TABLES.S Ql:r c:\scripts\table_inserts.sql:r c:\scripts\create_indexes.sql:r C:\Scripts\CREATE_PROCEDURES.sqlPRINT ' Create complete ' GO

Script 2:create_indexes.sql

/* CREATE INDEX */print ' start 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 stored procedure */print ' Creating 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            joins DBO. TIMECARD T on e.employeeid = T.employeeid    WHERE   e.employeeid = @EMPLOYEEID    ORDER by Dateworkedgo

Script 4:create_tables.sql

/* Create data Table */print ' creating data table ' Gouse testdbgoif object_id (' EMPLOYEE ') is not NULL     DROP table DBO. Employeegocreate TABLE DBO. EMPLOYEE    (      EMPLOYEEID INT IDENTITY (1, 1) not                     NULL                     PRIMARY KEY,      FIRSTNAME VARCHAR,      LASTNAME VARCHAR (goif)    object_id (' TIMECARD ') is a 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 is not NULL,      dateworked DATETIME is not null    ) Godeclare @TOTAL_T ABLES Intset @TOTAL_TABLES = 2

Script 5:table_inserts.sql

/* Insert Table Data */print ' total TABLES CREATED = ' + CAST (@TOTAL_TABLES as VARCHAR) goprint ' inserting data into table EMPLOYEE ' Gouse Testdbgoinsert Into  DBO. EMPLOYEE        (FIRSTNAME, LASTNAME)        SELECT  ' JOHN ',                ' DOE ' Goinsert into  DBO. EMPLOYEE        (FIRSTNAME, LASTNAME)        SELECT  ' JANE ',                ' DOE ' Goinsert 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:

Sqlcmd-e-dmaster-ic:\scripts\create_db.sqlpause

Step three: Execute the bat file directly under the C drive:

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, it executes directly and executes successfully.

Summarize:

Depending on your experience, it's better to develop a batch execution tool that can be used in a small number of scripts.

Execute multiple scripts in SQL Server using sqlcmd