skreebydba

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.