Executing multiple SQL Scripts

A customer asked if it was possible to execute multiple .sql scripts from a single command. I knew the SQLCMD utility could be used to work with input files and that a .bat file could be created executing SQLCMD commands one by one. Looking up the details of the SQLCMD utility I stumbled upon the :r <filename> command wich can be used to parse additional Transact-SQL statements and SQLCMD commands from the file specified by <filename> into the statement cache. So this means I can create a ‘master’ script calling the other scripts and just need to execute the master script with the SQLCMD utility. So I’m going to create a database, add some tables and insert some data from 3 different scripts.

First I create the CREATE_TABLES.sql script to create the tables (duh). Because I’m ‘parsing statements in the statement cache’ it should be possible to declare variables in one script and use them in another script. So I declare and fill a variable to hold the table count:

PRINT ‘Creating tables’
GO

USE Axel8s
GO

IF OBJECT_ID(‘Article’) IS NOT NULL
DROP TABLE dbo.Article
GO
CREATE TABLE dbo.Article
 (
 Article_ID int IDENTITY (1,1) NOT NULL PRIMARY KEY,
 Name varchar (50),
 Remark varchar (50)
 )
GO

IF OBJECT_ID(‘Category’) IS NOT NULL
DROP TABLE dbo.Category
GO
CREATE TABLE dbo.Category
 (
 Category_ID int IDENTITY (1,1) NOT NULL PRIMARY KEY,
 Article_ID int,
 DateAdded DATETIME
 )
GO

DECLARE @iTableCount int
SET @iTableCount = 2

The INSERT_DATE.sql script will insert some data and print the value of the variable from the CREATE_TABLES script:

PRINT ‘TOTAL TABLES CREATED = ‘ + CAST(@iTableCount AS varchar)
GO
PRINT ‘INSERTING DATA INTO Article’
GO

USE Axel8s
GO

INSERT INTO dbo.Article (Name, Remark)
 SELECT ‘Orange’,’The fruit ones’
GO
INSERT INTO dbo.Article (Name, Remark)
 SELECT ‘Coke’,’To drink’
GO
INSERT INTO dbo.Article (Name, Remark)
 SELECT ‘Hamburger’,’No not the germans’
GO

Now it’s time to create a MASTER.sql script that will create the database and will call the other 2 scripts:

SET NOCOUNT ON
GO

PRINT ‘CREATING DATABASE’
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = ‘Axel8s’)
DROP DATABASE Axel8s
GO
CREATE DATABASE Axel8s
GO

:On Error exit

:r c:\Axel8s\CREATE_TABLES.sql
:r c:\Axel8s\INSERT_DATA.sql

PRINT ‘DATABASE CREATION COMPLETED SUCCESFULLY’

Almost everything is in place now to start a double-click creation of my new database. The final script is a .bat file with the following command:

SQLCMD -S -E -dmaster -ic:\Axel8s\master.sql
PAUSE

And with a double-click on the create_Axel8s.bat file my new database is created:

 

Advertisements
%d bloggers like this: