Batches Submitted as Files

A batch is a set of SQL statements submitted together and executed as a group. A script is often a series of batches submitted one after the other. A batch is compiled only once as a whole and is terminated by an end-of-batch signal. Because of this, certain restrictions on batch size exist.

You can submit one or more batches of SQL statements to isql from an operating-system file. A file can include more than one batch ¾ more than one collection of SQL statements¾each terminated by the GO end-of-batch signal.

For details, see Batches in the Microsoft SQL Server Transact-SQL Reference.

The following excerpt of INSTPUBS.SQL script, located in the SQL Server INSTALL directory, contains the following batches that help to create the pubs sample database (note that comments have been removed):

SET NOCOUNT ON
SET DATEFORMAT mdy
USE master
go

CREATE TABLE #dbdev_temp
(
    name_var        varchar(30)     NOT NULL,
    type    varchar(10)     NOT NULL
)
go

INSERT #dbdev_temp VALUES('pubs', 'dbname')
INSERT #dbdev_temp VALUES('master', 'devname')
go

DECLARE @dbname_var     varchar(30)
SELECT @dbname_var = name_var FROM #dbdev_temp WHERE type = 'dbname'
IF EXISTS (SELECT * FROM master.dbo.sysdatabases
    WHERE name = @dbname_var)
BEGIN
    EXECUTE ('DROP DATABASE '  @dbname_var)
END
go

DECLARE @dbname_var     varchar(30),
    @devname_var    varchar(30)
SELECT @dbname_var = name_var FROM #dbdev_temp WHERE type = 'dbname'
SELECT @devname_var = name_var FROM #dbdev_temp WHERE type = 'devname'
EXECUTE ('PRINT "Creating the publishers database as '  @dbname_var
             ' on the '  @devname_var  ' device."')
EXECUTE ('CREATE DATABASE '  @dbname_var  ' ON '  @devname_var  ' = 3')
go

DECLARE @dbname_var     varchar(30)
SELECT @dbname_var = name_var FROM #dbdev_temp WHERE type = 'dbname'
EXECUTE ('USE '  @dbname_var)
go

DECLARE @dbname_var     varchar(30)
SELECT @dbname_var = name_var FROM #dbdev_temp WHERE type = 'dbname'
IF ((db_id() <> db_id(@dbname_var)) OR (db_id(@dbname_var) IS NULL) OR (db_id() = db_id('master')))
    RAISERROR ('The publishers database was not created. See the Errorlog or
Event Viewer for more details.',20,0) WITH LOG
go

EXECUTE ('USE master')
DECLARE @dbname_var     varchar(30)
SELECT @dbname_var = name_var FROM #dbdev_temp WHERE type = 'dbname'
EXECUTE ('sp_dboption '''  @dbname_var  ''', ''trunc. log on chkpt.'', true')
EXECUTE ('USE '  @dbname_var)
go

EXECUTE sp_addtype id, 'varchar(11)', 'NOT NULL'
EXECUTE sp_addtype tid, 'varchar(6)', 'NOT NULL'
EXECUTE sp_addtype empid, 'char(9)', 'NOT NULL'
go

CREATE TABLE authors
(
    au_id   id
    CHECK (au_id like '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]')
    CONSTRAINT UPKCL_auidind PRIMARY KEY CLUSTERED,
    au_lname        varchar(40)     NOT NULL,
    au_fname        varchar(20)     NOT NULL,
    phone   char(12)        NOT NULL
        DEFAULT ('UNKNOWN'),
    address varchar(40)     NULL,
    city    varchar(20)     NULL,
    state   char(2) NULL,
    zip     char(5) NULL
        CHECK (zip like '[0-9][0-9][0-9][0-9][0-9]'),
    contract        bit     NOT NULL
)
go
.
.
.