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.
