Executing a TSQL batch multiple times using GO

Using the GO commend in TSQL to run a batch of code multiple times is something that I commonly do in demo’s and training’s.  What amazes me is how many people after the training will tell me that they didn’t know about using the GO command to run a statement multiple times.

Here’s how it works. First lets create a table, then we will insert 100000 into the table using a while loop.

CREATE TABLE SampleTable(
	ID INT NOT NULL IDENTITY(1,1),
	SomeData uniqueidentifier NOT NULL DEFAULT(NEWID())
);

DECLARE @i INT; 
SET @i = 1;
WHILE @i <= 100000
BEGIN
	INSERT dbo.SampleTable DEFAULT VALUES ;
	SET @i = @i + 1;
END 

Sure that works, but it takes several lines of code to loop. There is an easier way to do it.

CREATE TABLE SampleTable(
	ID INT NOT NULL IDENTITY(1,1),
	SomeData uniqueidentifier NOT NULL DEFAULT(NEWID())
);

GO
	INSERT dbo.SampleTable DEFAULT VALUES; 
GO 100000

In this example, GO is the batch separator, SSMS breaks up the TSQL on GO statements, then runs each batch one at a time.
The first GO command is used to simply separate the CREATE statement from the INSERT statement. Then the second GO with followed by 100000 tells SSMS to run that batch 100000 times. If we didn’t have the first GO, the CREATE TABLE would have been attempted 100000 times, not just the INSERT STATEMENT.

GO IS NOT A TSQL KEYWORD.

GO is simply a SSMS batch separator word that can be modified to be something else in the SSMS settings if you want to change it. GO is not part of TSQL, and won’t work when you call TSQL from your own programs.

A quick tip that will hopefully save you some time.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!