Executing a TSQL batch multiple times using GO

By:   |   Updated: 2024-06-21   |   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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2024-06-21

Comments For This Article




Saturday, August 28, 2021 - 8:07:22 AM - Ricardo Back To Top (89182)
And I thought it would be a call using GO (the language) :)

Friday, August 27, 2021 - 8:58:58 AM - Greg Robidoux Back To Top (89177)
Thanks John.

Friday, August 27, 2021 - 5:00:45 AM - John Perry Back To Top (89176)
Greg, The GO batch separator is a function of the Client (typically SSMS) and not of SQL Server itself.

Thursday, August 19, 2021 - 5:58:02 PM - Greg Robidoux Back To Top (89152)
Hi Tony,

Never tried it a job step before. It could be the GO command is just recognized in a query window.

I'll do some testing to see what I find out.

Glad you found a work around. Another option is to write a while loop.

-Greg

Thursday, August 19, 2021 - 4:33:43 PM - Tony Carl Back To Top (89151)
An agent has a job that executes a SP to delete top(4000) rows older than <days parameter>.
I wanted to alter the job to execute the SP five times to draw down the history a bit faster.
I added "go 5" after the execution statement - but it still executed only once.
Curious!
I simply copy&pasted the execute statement four more times... it now deletes 20000 rows.

Thursday, August 19, 2021 - 12:55:26 PM - Greg Robidoux Back To Top (89149)
Hi Tony, can you elaborate on what you have setup and what you are trying to do.

Thanks
Greg

Thursday, August 19, 2021 - 10:26:19 AM - Tony Carl Back To Top (89148)
Interesting that when I use this method for multiple executions in an agent job step - it only executes the "batch" once.

Thursday, April 16, 2015 - 4:53:53 PM - Chander Back To Top (36954)

Ashamed without knowing these till now. Feel guilty when i look at my codes that are using 'While' loop. Will rewrite if there is no performance hunch. Thanks a bunch


Friday, January 30, 2015 - 1:58:40 AM - rajendra Back To Top (36109)

Hi Greg,

As the cleandata process from my asp.net web application, If I want to clean historical data from a bunch of tables(around 50 plus tables, some tables are really large) from a database, is it a good practise to include GO after each delete statement or can I simply write all delete statements without a Go included. Can you please explain.

Thankyou

 


Tuesday, May 28, 2013 - 5:04:18 AM - wattyjnr Back To Top (25163)

Excellent! Thanks very much Greg.


Sunday, May 26, 2013 - 6:33:28 PM - Greg Robidoux Back To Top (25147)

@wattyjnr - I don't think that is possible. 

You could create a loop as follows:

DECLARE @NoTimesToExecute INT = 2
DECLARE @counter int = 1

WHILE @counter <= @NoTimesToExecute
BEGIN
 SELECT TOP 1 * FROM sys.sysdatabases
 SET @counter = @counter + 1
END


Friday, May 24, 2013 - 10:26:21 AM - wattyjnr Back To Top (25127)

Hi Greg,

Thanks for the nice tip. Is there anyway that I can use a parameter to derive the number of times to execute the GO statement, or do I always have to use a hard coded value?

 

i.e.

DECLARE @NoTimesToExecute INT = 2

INSERT INTO <schema>.<table> DEFAULT VALUES

GO @NoTimesToExecute


Friday, March 23, 2012 - 9:46:07 AM - nishant Back To Top (16592)

Greg Thank you so much for this bit

i did not know this earlier and liked it .

Keep posting..


Thursday, April 21, 2011 - 12:43:02 PM - Greg Robidoux Back To Top (13660)

Hi Kevin,

Not totally sure what you need, but you can either pass in the variable which means you would need to modify your SPs if you are passing it in as another parameter or do what you are mentioning with a global ## temporary table.

Greg


Thursday, April 21, 2011 - 12:26:30 PM - Kevin Back To Top (13658)

Ok.  I need to stop using IE9.

 

Is there a way to keep the current iteration of the batch?  Using a variable like @GoCount won't work, because it goes out of scope with each time.  I can write it to a temp table and increment, but that seems a little heavy-weight.  Is there a method that is more straight-forward?


Saturday, August 21, 2010 - 8:18:39 AM - JTM Back To Top (10068)
Hallo.can someone please help me with sql batches , i really dont know how to use them even though they seem to be the answer to the problem i have.here is my problem: im trying to write a student management system and the problem i have is to link student to their classes.the relationship beetween student and class is a many to many and i broke it with a StudentClass Table.so the problem im encountering now is to choose one student and assign him/her to their classes.and im using a checkedlist box control to poplulate classes and when i try to assign as many classes to a student,im getting a Violation of Foriegn Key constraint error and each time i use the GO keyword in my VB application, it is not recognise,so i don't really know how to approach the problem,please help.Thank you















get free sql tips
agree to terms