Home > SQL Server, T-SQL > What Not to Do When Using GO with a Count

What Not to Do When Using GO with a Count

Recently, I learned you can put a GO statement after a block of SQL code followed by a count. This will execute the code count number of times. It is a handy way to quickly replace a WHILE loop.

I was running a test this morning, needing to insert data into a table and begin an explicit transaction. To do this, I executed the following code (don’t try this at home):

BEGIN TRANSACTION
INSERT INTO #table
VALUES
(1
,'text')
GO 1000000

What I didn’t consider is all of the statements above the GO would be executed 1 million times, including the BEGIN TRANSACTION. I figured it out when I tried to run a SELECT against the temp table and it hung. I went back to the INSERT statement, issued a commit and the SELECT was still hung. After about 5 more COMMIT statements, I realized each execution of the GO opened an explicit transaction. Thankfully, the following statement solved my problem:

COMMIT
GO 1000000

And that is one to grow on.

Categories: SQL Server, T-SQL Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: