Loop in SQL Server without For,while, goto


Hi Every one

Usually we all use a While, for or some old timers use GOTO to loop a batch. but there is some other way to do the same.


create table emp
(
id  INT IDENTITY(1,1) PRIMARY KEY
,name varchar(200) NULL
)
GO

insert into emp (name) values ('suri')
GO 100

 

The above insert statement will insert 100 records into the table emp even though we have not mentioned any loop, The trick is to mention the number after the GO statement. It will loop through the complete batch (last GO statement to the current statement or from the start of the script).

Drawbacks: This can cause a serious trouble if we dont use the batch delimiter before the statement we want to execute, for example if we are missing the first GO statement we will get an error saying the table emp already exists, as it will try to execute the create table statement as well.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s