SQL Server with Mr. Denny

Oct 17 2011   2:00PM GMT

Quick and dirty way to run a batch over and over

Denny Cherry Denny Cherry Profile: Denny Cherry

Everyone has their favorite way or running a block of code over and over again.  If you have to run an entire batch over and over again there is nothing as quick and easy as using the “GO n” syntax.  When you put a number after the GO batch separator SQL Server Management Studio (as well as SQLCMD) will run that entire batch however many times that you have specified.

Recently I needed to create some dummy data in a table, so instead of writing a loop that counted the number of times the loop ran, I simply used code like this.

CREATE TABLE SampleData
(id int identity(1,1),
SomeData varchar(100))
go
insert into SampleData
(SomeData)
values
('This is my sample data')
go 100000

When run, the batch with the insert statement ran 100,000 times generating all the data that I needed with just 7 extra keystrokes.

Denny

SQL Server Days 2011

1  Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • JanNovak
    Hi Denny. Thanks for a great post. I just want to add my experience. GO n or using WHILE for inserting data is much slower then using set based solution: [CODE] INSERT INTO SampleData(SomeData) SELECT TOP 100000 'This is my sample data' FROM [master].[dbo].[spt_values] t1 CROSS JOIN [master].[dbo].[spt_values] t2 [/CODE] Have a nice day Jan
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: