Executing a TSQL batch multiple times using GO

By: Greg Robidoux   |   Updated: 2021-08-09   |   Comments (16)   |   Related: More > Scripts

Problem

Sometimes there is a need to execute the same command or set of commands over and over again. This may be to insert or update test data or it may be to put a load on your server for performance testing. One way to do this is to setup a while loop and execute the code over and over again, but there is another way this can be done with the GO command.

Solution

The GO keyword tells SQL Server to execute the preceding code as one batch. In SQL Server, you have the ability to add a number after the GO command to tell SQL Server how many times to execute the batch. So, let's take a look at a couple of examples.

Use GO to execute a SQL batch X number of times

Let's say you want to create a test table and load it with 1000 records. You could issue the following command and it will run the same INSERT command 1000 times:

CREATE TABLE dbo.TEST (ID INT IDENTITY (1,1), ROWID uniqueidentifier)
GO 
	
INSERT INTO dbo.TEST (ROWID) VALUES (NEWID()) 
GO 1000

Another example of using GO with a SQL Server batch

Here is another example that executes two INSERT statements 100 times. As you can see you can add more statements to the batch to be run (two inserts in this case), X number of times specified after GO command.

CREATE TABLE dbo.TEST (ID INT IDENTITY (1,1), ROWID uniqueidentifier) 
CREATE TABLE dbo.TEST2 (ID INT IDENTITY (1,1), ROWID uniqueidentifier) 
GO  
		
INSERT INTO dbo.TEST (ROWID) VALUES (NEWID()) 
INSERT INTO dbo.TEST2 (ROWID) VALUES (NEWID()) 
GO 100

Using a loop to run a batch over and over again

To do something similar with a loop, you would need to write code such as the following. It is not that big a deal, but writing GO 100 seems a bit easier to me.

CREATE TABLE dbo.TEST (ID INT IDENTITY (1,1), ROWID uniqueidentifier)
CREATE TABLE dbo.TEST2 (ID INT IDENTITY (1,1), ROWID uniqueidentifier)
GO 

DECLARE @counter INT 
		
SET @counter = 0 
		
WHILE @counter < 100
BEGIN 
   INSERT INTO dbo.TEST (ROWID) VALUES (NEWID())
   INSERT INTO dbo.TEST2 (ROWID) VALUES (NEWID())
   SET @counter = @counter + 1
END

Next Steps

• Add this new trick to your list of tricks to make running the same batch faster then having to write a while loop
• Check out these other SQL Server tips:
  • Improve Performance with SQL Covering Index Enhancements
  • BCP XML Format Files with SQL Server

About the author

Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips