T-SQL Tips -- GO # and WHILE Loops

Two quick tricks: populating a table with data quickly, and boosting performance with WHILE loops.

• By Joshua Jones
• 07/12/2010

While there are a great many books, blogs, and articles out there on T-SQL, there are always a few little "tricks" that many people overlook, or that many authors assume readers know. So this week, let's take a quick look at two neat little T-SQL tricks that can make your life a little easier.

GO (#)
This is very simple. While most of us are familiar with the batch separator 'GO', many don't realize that you can pass a number to it to tell SQL Server to run that preceding batch a specific number of times. For example:

create table MyTable
(   colA int identity(1,1),
    colB varchar(10)
)
go
insert into MyTable VALUES ('Text')
go

select * from MyTable

From this, I've created a table and inserted a single value into it (see Fig. 1). The results of the select are obvious and can be seen in Fig. 1.

Figure 1. What you select will end up in this table.

However, if I add a 5 after the GO in the INSERT statement and run both it and the SELECT, here's what you get (see Fig. 2 for the result):

insert into MyTable VALUES ('Text')
go 5
select * from MyTable

Figure 2. Populating your table with more data.

Simple! This can be useful for generating quick test data, or repeatedly running a query where you don't want to take time to write a WHILE loop or cursor.

WHILE Loops
Speaking of those WHILE loops and cursors: Many people find themselves writing cursors when they need to iterate through a query. However, most of us know that cursors CAN perform poorly (though not always).

If you want to run a piece of code over and over, without using a cursor, you can use a WHILE loop instead. This is particularly useful in scripts and/or stored procs, since it does take a little setup.

Consider this script:

declare @min int,
@max int,
@sql varchar(max)

create table NewTable
(   ID int identity(1,1),
    String varchar(20)
)

set @min = (select MIN(colA) from MyTable)
set @max = (select MAX(colA) from MyTable)

WHILE @max >= @min
BEGIN
    set @sql = 'insert into NewTable VALUES (''Text'')'
    exec (@sql)
    set @min = @min + 1
END

select * from NewTable
 
After declaring some variables (which we'll come back to), I've created a new table. Now, let's pretend I need to move some data from the table in my previous example to this new one. I can use the built-in IDENTITY columns to define a low and high range of values (in more complicated scenarios, you'd also use a WHERE clause). So, I set my @min variable to the low end, and my @max variable to the high end. Now, I can compare the two, and use a WHILE loop to move through my range of rows.

Yes, this can be accomplished via a cursor. And for many people, because they've had to write a number of cursors, a cursor can seem easier. But, this method is an alternative that is often faster (in terms of execution) than a cursor.

So there you go, two simple T-SQL tricks that can make your life a little easier. Have fun!