GO – repeating batches

GO is very cool. More so than you might think, and in a couple of interesting ways. This is the 6th tip from my TechEd Australia talk, from which you can see my slides and scripts in other posts.

In SQL Server, GO is the traditional batch separator. But what many people don’t know (but has been mentioned around the blogosphere several times over the past year or so), is that you can put a number after GO to make the batch run repeatedly. I often use this method to populate lots of sample data into a table.

INSERT dbo.someTable (col1, col2)
SELECT col1, col2
FROM dbo.someTable;
GO 5

If my table starts with 10 rows, it will insert a copy of those ten rows, then twenty, then forty, eighty, and finally an extra 160, leaving me with 320 rows in my table. I guess I could have hit F5 5 times, but you get the point.

The fun side of GO is that it’s a configurable option in Management Studio. Head into Tools, Options, and you’ll see it under Query Execution.

Now, I would never condone changing this on a colleague’s machine. If you do, then GO will start causing errors. Scripts that once worked will probably start failing (but not necessarily all – simple queries might just think GO is an alias).

But if you want to be really mean – and again, I would never suggest you do this to that annoying colleague who always leaves their laptop unlocked when they head off to lunch, even if they just live to frustrate every waking minute of your life – if you want to be really mean, you set the Batch separator to SELECT.

I’m sure your eyes have just dilated at the pure evil. And so they should. Now, even the most basic of queries will give the nastiest of errors. Restarting SSMS won’t fix it. Rebooting won’t fix it. You had better make sure you don’t consider doing this to a colleague – if they realise (having searched online for “A fatal scripting error occurred. Incorrect syntax was encountered while parsing SELECT.” and found this post), they may figure it out and it won’t be good for you. At least make sure you’re around to help them when they first panic.