THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Did You Know? Run a batch multiple times

 

At first I thought I would just add another sentence to my previous post, but then I realized that this feature is not even documented anywhere, and it might be useful to mention it in its very own post.  I have no idea why it is undocumented, but it has been part of all command interfaces to TSQL that I have used for the last 20 years.

When you use GO as a batch terminator, you can follow it by an integer, which indicates that the batch should be executed N times.

I use this frequently when populating test tables. In my previous post, I ended with this suggestion:

-- You can add more rows by just rerunning the INSERT statement from above, as many times as you would like.

 

So, to run the INSERT 5 times, I would do this:

INSERT INTO details
(SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID,
UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate)
        SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID,
             UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate
        FROM Sales.SalesOrderDetail
GO 5

 

Have fun!

~Kalen

Published Sunday, July 22, 2007 4:08 PM by Kalen Delaney
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Jamie Thomson said:

Well blow me down. I never knew that.

Thanks kalen.

July 22, 2007 7:33 PM
 

Uri Dimant said:

Hi Kalen

It's just great.Unfortunately it does not work on SQL Server 2000.

July 22, 2007 11:40 PM
 

James Luetkehoelter said:

That's news to me as well - does it work if you specify a different batch delimiter other than "GO" in the Query Options? Dang, now I have to know....all I need late at night to be obsessed with finding an answer.

July 22, 2007 11:51 PM
 

ACALVETT said:

Uri,

It seems that it does not work within Enterprise Manager or Query Analyser. But it does work with OSQL. Also works using SSMS against SQL 2000 so its down to the "tool" your using.

July 23, 2007 8:05 AM
 

Olivier Moreau said:

Excellent !!!

and very usefull to do quickly load testing !!

July 24, 2007 9:19 AM
 

Roy Harvey said:

Interesting, and quite surprising that it has been around all this time.  I don't know that I will ever use it, but You Never Know.

SQLCMD allowed using a scripting variable to set the number of repeats.

I did find one fairly insignificant difference between OSQL and SQLCMD.  Using OSQL a value of zero gave the same results as a value of 1, but a negative number gave an error.  Using SQLCMD a value of zero gave zero repetitions, as did a negative number.

July 29, 2007 6:03 PM
 

dinakar said:

Hi Kalen

Just came across this. thats a neat trick. How do you even find these things?

I think I can find few scenarios I can use it :)

August 3, 2007 4:48 PM
 

Kalen Delaney said:

Last July, I posted about a way to run a batch multiple times when you use GO as the batch separator.

October 24, 2007 3:28 PM
 

Kalen Delaney said:

Last July, I posted about a way to run a batch multiple times when you use GO as the batch separator.

November 4, 2007 9:48 PM
 

Denis Gobo said:

A year in review, The 21 + 1 best blog posts on SQLBlog Best posts according to me, it might have been

December 27, 2007 4:11 PM
 

Kalen Delaney said:

I know, I promised a technical post after my online seminar today, and I'm still planning on that. There

June 30, 2010 5:33 PM
 

Kalen Delaney said:

I know, I promised a technical post after my online seminar today, and I'm still planning on that. There

June 30, 2010 5:35 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement