Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Executing a TSQL batch multiple times using GO

By:   |   Updated: 2007-04-05   |   Comments (9)   |   Related: More > Scripts

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.  Whatever the need the easiest way to do this is to setup a while loop and execute your code, but in SQL 2005 there is an even easier way to do this.

In both SQL Server 2000 and SQL Server 2005 the keyword GO tells SQL Server to execute the preceding code as one batch.  In SQL Server 2005 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:

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 command 1000 times:

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

Here is another example that executes both INSERT statements 1000 times.   As you can see you can add more and more statements to the batch to be run the set number of times that is specified after the GO.

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

To do something similar to this in SQL Server 2000 you would need to write code such as the following. It is not that big a deal, but writing GO 1000 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)

DECLARE @counter INT 
WHILE @counter 1000 
SET @counter @counter 1

Next Steps

Last Updated: 2007-04-05

get scripts

next tip button

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

View all my tips

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 

Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.


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

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

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.



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

Excellent! Thanks very much Greg.

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

@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
 SELECT TOP 1 * FROM sys.sysdatabases
 SET @counter = @counter + 1

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

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?



DECLARE @NoTimesToExecute INT = 2


GO @NoTimesToExecute

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

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

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.


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

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
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

Learn more about SQL Server tools