In this demonstration, we are going to look at working with batches. To begin our example, we will need to run both the CREATE SCHEMA and CREATE TABLE statements to hold sample data. Then we will use two INSERT statements to add data.

Notice we do not have a GO statement on line 7 to divide the CREATE statements from the INSERT statements into separate batches. Additionally, we are misspelling the word VALUSE on line 12.

CREATE SCHEMA Accounting Authorization dbo
CREATE TABLE BankAccounts
  (AcctID int IDENTITY,
   AcctName char(15),
   Balance money,
   ModifiedDate date)


INSERT INTO Accounting.BankAccounts
VALUES('Jack',500, GETDATE())
INSERT INTO Accounting.BankAccounts
VALUSE('Diane', 750, GETDATE())
GO

This will produce the following errors:

The first error happens because batches are used to divide code into sections for Parsing Syntax and Resolving Object names. The Msg 156 error is an example of an object resolution error and occurs because SQL has not yet created the SCHEMA or TABLE objects. These objects are needed so that the INSERT statements can add data. In addition, DDL statements such as the CREATE SCHEMA statement need to be in their own batches and will generate a syntax error as well. The Msg 102 error is also an example of a parsing syntax error and occurred because the keyword VALUES was misspelled.

We will add the GO statement to line 7 to divide the two code sections into separate batches. Notice we will not correct the misspelled keyword VALUES on line 12.

CREATE SCHEMA Accounting Authorization dbo
CREATE TABLE BankAccounts
 (AcctID int IDENTITY,
  AcctName char(15),
  Balance money,
  ModifiedDate date)
GO

INSERT INTO Accounting.BankAccounts
VALUES('Jack',500, GETDATE())
INSERT INTO Accounting.BankAccounts
VALUSE('Diane', 750, GETDATE())
GO

When we run this code, we will still get the Parsing Syntax error.

However, our Accounting Schema and BankAccounts Table have both been created. This is because the CREATE statements were processed in their own batch and ran without error. Also, notice that when we run a SELECT statement on the table no records will be displayed.

This is because there was a syntax error in the batch that included both INSERT statements which caused the entire batch to fail. Next, we will fix the misspelling of the keyword VALUES.

INSERT INTO Accounting.BankAccounts
VALUES('Jack',500, GETDATE())
INSERT INTO Accounting.BankAccounts
VALUES('Diane', 750, GETDATE())
GO

When we run this batch, we get the following message. This is because even though the two INSERT statements are in the same batch, they are still two separate implicit transactions.

Side Note: The difference between a batch and a transaction is that a batch is used to send code to the processor, while a transaction is used to send data modifications to the database. You can have many transactions inside a batch or many batches inside a transaction.

If we run the SELECT statement again, we will see that both INSERT statements ran and added the appropriate records.

One more item of concern with batches is that they not only create a boundary for syntax and parsing but also, they are boundaries for variables. For this example, we will declare and initialize a variable and then SELECT the variable in two separate batches.

DECLARE @intExample as int = 5
SELECT @intExample
GO

SELECT intExample

While the first batch ran successfully and displayed the number 5, the second batch will cause the error below to happen. This is because the variable only exists within the batch where it was created.