Using SQLCMD to Execute Multiple SQL Server Scripts

Problem

My company has a number of SQL installation scripts that build a fresh copy of our database. Each script relates to a specific SQL task. One script builds the database, another builds the tables, etc. I call SQLCMD repeatedly to execute each script. Is it possible to concatenate these scripts together and execute them with a single call to SQLCMD?

Solution

Yes it is. SQLCMD offers the :r command. When :r is encountered in a SQL script, it tells the SQLCMD utility to include the referenced file into the calling script. This give you the opportunity to break up a single script into multiple scripts. Each script will represent a functional area of the database. Alternatively, you can replace multiple SQLCMD calls with a single call to a main SQL file. Furthermore, using the :r command to execute multiple scripts in a single batch allows you define a single set of variables which can be seen by all the included scripts (provided there is no intervening GO terminator).

SQLCMD was introduced in SQL Server 2005 and is the replacement for osql which Microsoft will be deprecating in a future release. If you’re not familiar with SQLCMD, it is a command line utility that can be used to execute T-SQL commands and scripts from the operating system.

SQLCMD Example

In the forthcoming example, I’ll create 5 .sql files representing functional areas of a sample database called MSSQLTIPS. The first script is called CREATE_DB.sql and creates a database on your SQL Server called MSSQLTIPS. This script includes 4 scripts (using the :r command) to perform table creation, table inserts, index creation, and stored procedure creation. A .bat file is created to execute SQLCMD from the Windows operating system in order to create a fresh database.

I first create a sub-folder under my C: drive called C:\Scripts. I store the following SQL scripts in this folder.

Create Tables Script

/* SCRIPT: CREATE_TABLES.sql */
PRINT 'CREATING TABLES '
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

Insert Data Code

/* SCRIPT: TABLE_INSERTS.sql */
 
PRINT 'TOTAL TABLES CREATED = ' + CAST(@TOTAL_TABLES AS VARCHAR)
GO
PRINT 'INSERTING DATA INTO EMPLOYEE'
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

Build SQL Server Indexes

/* SCRIPT: CREATE_INDEXES.sql */
PRINT 'CREATING INDEXES'
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

Create SQL Stored Procedures

/* SCRIPT: CREATE_PROCEDURES.sql */
PRINT 'CREATING PROCEDUREs'
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 to Create New Database and Objects

/* SCRIPT: CREATE_DB.sql */
/* BUILD A DATABASE */
 
-- This is the main caller for each script
SET NOCOUNT ON
GO
 
PRINT 'CREATING DATABASE'
IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = 'MSSQLTIPS')
DROP DATABASE MSSQLTIPS
GO
CREATE DATABASE MSSQLTIPS
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 'DATABASE CREATE IS COMPLETE'
GO

Run All Script Files

In the root C:\ folder, I create a file called create_db.bat which I use to create the database with all objects

SQLCMD -E -dmaster -ic:\Scripts\create_db.sql
PAUSE

Double clicking the .bat file, I see that each script processed and that the database created successfully.

The SQLCMD utility offers a wealth of commands that can used to modify the .bat file to further control and refine output and error behavior.

Next Steps

• Read more about the wealth of other SQLCMD commands
• Read this SQLCMD tutorial
• NOTE: You can download the sample files via the attached .zip file. Unzip the file and execute the sample code that will be presented in this tip.

MSSQL Tips

For 20+ years, MSSQLTips.com has been delivering comprehensive technical solutions on a daily basis for the Microsoft SQL Server platform.  In that time, an innovative team of over 300 authors from around the globe have delivered over 7000 articles and 1000 webcasts ranging from beginner topics to advanced solutions.  Our community prides itself on delivering human only generated content.