How To Use SQLCMD Utility in SQL Server 2008?

Introduction

SQLCMD was introduced in SQL Server 2005. 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.

Features

• The sqlcmd utility lets you enter Transact-SQL statements, procedures, and script files at the command prompt, in Query Editor in SQLCMD mode.
• This utility uses OLE DB provider to execute Transact-SQL batches.

Important Note: SQL Server Management Studio uses the Microsoft.NET FrameworkSqlClient for execution and SQLCMD mode in Query Editor. Because different default options may apply, you might see different behavior when you execute the same query in SQL Server Management Studio in SQLCMD Mode and in the sqlcmd utility.

In the below example, I am going to create 5 .sql files under a newly created database called EMPLOYEE_DETAILS. The first script is called CREATE_DB.sql and creates a database on your SQL 2008 Server called EMPLOYEE_DETAILS. This script then includes 4 scripts (using the :r command) to perform table creation, table data 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 new database.

So let’s begin...

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

SQL SCRIPT TO CREATE TABLES

/* CREATE_TABLES.sql */

PRINT 'CREATING TABLES '
GO

IF OBJECT_ID('EMPLOYEE') IS NOT NULL
DROP TABLE DBO.EMPLOYEE
GO

CREATE TABLE EMPLOYEE
(
   EMP_ID		INTEGER PRIMARY KEY,
   EMP_NAME		VARCHAR (10),
   DESIGANTION	VARCHAR (9),
   GRADE		INTEGER NULL,
   HIRE_DATE		DATETIME,
   SALARY		NUMERIC (7,2),
   DEPT_ID		INTEGER
)
GO
IF OBJECT_ID('DEPARTMENT') IS NOT NULL
DROP TABLE DBO.DEPARTMENT
GO

CREATE TABLE DEPARTMENT
(
   DEPT_ID		INTEGER NOT NULL,
   DEPT_NAME	VARCHAR (14),
   LOCATION		VARCHAR (13)
)
GO

DECLARE @TOTAL_TABLES INTEGER
SET @TOTAL_TABLES = 2

SQL SCRIPT TO INSERT DATA

/* TABLE_INSERTS.sql */

PRINT 'TOTAL TABLES CREATED = ' + CAST(@TOTAL_TABLES AS VARCHAR)
GO
PRINT 'INSERTING DATA INTO EMPLOYEE'
GO

INSERT INTO EMPLOYEE VALUES 
	(1,'JOHNSON','HR',6,'12-17-2010',18000,1)
GO
INSERT INTO EMPLOYEE VALUES 
	(2,'MARC','MANAGER',9,'02-02-2011',52000,3)
GO
INSERT INTO EMPLOYEE VALUES 
	(3,'TAFT','SALES I',2,'01-02-2009',25000,3)
GO
INSERT INTO EMPLOYEE VALUES 
	(4,'BUNNY','SALES I',2,'04-02-2010',27000,3)
GO
INSERT INTO EMPLOYEE VALUES 
	(5,'ROHIT','TECH',6,'06-23-2008',22500,1)
GO
INSERT INTO EMPLOYEE VALUES 
	(6,'GARFIELD','MANAGER',9,'05-01-2007',54000,2)
GO
INSERT INTO EMPLOYEE VALUES 
	(7,'POLK','TECH',6,'09-22-1997',25000,4)
GO
INSERT INTO EMPLOYEE VALUES 
	(8,'GRANT','ENGINEER',10,'03-30-2007',32000,2)
GO
INSERT INTO EMPLOYEE VALUES 
	(9,'BHUSHAN','CEO',NULL,'01-01-2010',75000,2)
GO
INSERT INTO EMPLOYEE VALUES 
	(10,'FILLMORE','MANAGER',9,'08-09-2008',56000,2)
GO
INSERT INTO EMPLOYEE VALUES 
	(11,'ADAMS','ENGINEER',10,'03-15-2009',34000,2)
GO
INSERT INTO EMPLOYEE VALUES 
	(12,'WASHINGTON','ADMIN',6,'04-16-2011',18000,4)
GO
INSERT INTO EMPLOYEE VALUES 
	(13,'MONROE','ENGINEER',10,'12-03-2013',30000,2)
GO
INSERT INTO EMPLOYEE VALUES
(14,'ROOSEVELT','CPA',9,'10-12-2007',35000,1) 
GO


PRINT 'TOTAL TABLES CREATED = ' + CAST(@TOTAL_TABLES AS VARCHAR)
GO
PRINT 'INSERTING DATA INTO DEPARTMENT'
GO

INSERT INTO DEPARTMENT VALUES (1,'IT','INDIA')
GO
INSERT INTO DEPARTMENT VALUES  (2,'RESEARCH','NEW YORK')
GO
INSERT INTO DEPARTMENT  VALUES (3,'SALES','CHINA')
GO
INSERT INTO DEPARTMENT  VALUES (4, 'OPERATIONS','JAPAN')

SQL SCRIPT TO CREATE INDEXES

/* SCRIPT: CREATE_INDEXES.sql */

PRINT 'CREATING INDEXES'
GO

IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WHERE NAME = 'EMP_NAME')
CREATE INDEX EMP_NAME ON DBO.EMPLOYEE(EMP_ID, EMP_NAME)
GO

IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WHERE NAME = 'DEPT_ID')
CREATE INDEX DEPT_ID ON DBO.DEPARTMENT(DEPT_ID)
GO

SQL SCRIPT TO CREATE STORED PROCEDURE

/* CREATE_PROCEDURES.sql */

PRINT 'CREATING PROCEDURE'
GO

IF OBJECT_ID('GET_EMPLOYEE_DEPARTMENTS') IS NOT NULL
DROP PROCEDURE DBO.GET_EMPLOYEE_DEPARTMENTS
GO
CREATE PROCEDURE DBO.GET_EMPLOYEE_DEPARTMENTS @DEPT_ID INT
AS

SET NOCOUNT ON
SELECT *
FROM DBO.DEPARTMENT D
JOIN DBO.EMPLOYEE E ON D.DEPT_ID = E.DEPT_ID
WHERE D.DEPT_ID = @DEPT_ID
GO

SQL SCRIPT TO CREATE NEW DATABASE & OBJECTS

/* CREATE_DB.sql */

SET NOCOUNT ON
GO

PRINT 'CREATING DATABASE'
IF EXISTS (SELECT 1 FROM SYS.DATABASES WHERE NAME = 'EMPLOYEE_DETAILS')
DROP DATABASE EMPLOYEE_DETAILS
GO
CREATE DATABASE EMPLOYEE_DETAILS
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

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

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

Now, double click .bat file, you can see that each script processed and that the new database along with its objects created successfully as shown above. Hope this tip will guide you to understand the use of SQLCMD utility in the near future.