HEELPBOOK - EXECUTING A T-SQL BATCH MULTIPLE TIMES USING GO (SQL SERVER) ################################### 


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


####### Solution

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
INSERT INTO dbo.TEST (ROWID) VALUES (NEWID())
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
INSERT INTO dbo.TEST (ROWID) VALUES (NEWID())
INSERT INTO dbo.TEST2 (ROWID) VALUES (NEWID())
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)
GO
DECLARE @counter INT
SET @counter = 0
WHILE @counter < 1000
BEGIN
INSERT INTO dbo.TEST (ROWID) VALUES (NEWID())
INSERT INTO dbo.TEST2 (ROWID) VALUES (NEWID())
SET @counter = @counter + 1
END



############ ARTICLE INFO #############

Article Month: December
Article Date: 31/12/2012
Permalink: http://heelpbook.altervista.org/2012/executing-a-t-sql-batch-multiple-times-using-go-sql-server/comment-page-1/
Source: http://www.mssqltips.com/sqlservertip/1216/executing-a-tsql-batch-multiple-times-using-go/
Language: English

View more articles on: http://www.heelpbook.net/
Follow us on Facebook: http://it-it.facebook.com/pages/HeelpBook/100790870008832
Follow us on Twitter: https://twitter.com/#!/HeelpBook
Follow us on RSS Feed: http://feeds.feedburner.com/Heelpbook
Follow us on Delicious: http://delicious.com/heelpbook
Linkedin: http://it.linkedin.com/pub/stefano-maggi/27/73a/b20
Google+ : https://plus.google.com/116990277568167008289/posts