Andras

Software Architect - Red Gate Software

The GO command can have a parameter?

Published Monday, September 24, 2007 1:57 PM

I have mixed feelings about the GO command. It is not a T-SQL statement, it is just something that Management Studio and the other SQL Server tools understand as a batch separator command. Indeed, you can change it to whatever you wish in Management Studio under Tools->Options->Query Execution->SQL Server->General->Batch separator.
So you can write queries like

SELECT * FROM sys.objects
foo
SELECT * FROM sysobjects
foo

Of course I'm still struggling to find a reason why someone would change the GO command.

One thing I've found out recently is its parameter. SQL Server Management Studio seems to accept an integer after the GO command, and this will start an execution loop. For example if you write

PRINT 'Hello word'
GO 5

The result will be:

Beginning execution loop
Hello word
Hello word
Hello word
Hello word
Hello word
Batch execution completed 5 times.


This is perfect for lazy moments when I want to populate a test table with some default values like:

CREATE TABLE foo
    ( a INT PRIMARY KEY IDENTITY
    , b INT DEFAULT 1
    )
GO

I usually write something like:
INSERT TOP (10) INTO foo (b) SELECT 1 FROM sys.objects

but with the parameter to the GO command the above can be achieved with even less typing:

INSERT INTO foo DEFAULT VALUES
GO 10

The above will also insert 10 rows :)
Do let me know if you find a more interesting use for this parameter.

    Andras
by András

Comments

 

Tech Talk with Brett Maytom said:

An old dog is never to old to learn new tricks, well that what I was thinking is after I read Andras
September 26, 2007 2:02 PM
 

GSquared said:

Wow.  Never knew that.  Never would have thought to try it.  I can think of several uses.
October 1, 2007 1:03 PM
You need to sign in to comment on this blog

About András

András Belokosztolszki is a software architect at Red Gate Software Ltd. He is a frequent speaker at many UK user groups and events (VBUG, NxtGen, Developer’s Group, SQLBits). He is primarily interested in database internals and database change management. At Red Gate he has designed and led the development of many database tools that compare database schemata and enable source control for databases (SQL Compare versions 4 to 7), refactor databases (SQL Refactor) and show the history of databases by analyzing the transaction log (SQL Log Rescue). András has a PhD from Cambridge and an MSc and BSc from ELTE, Hungary. He is also a MCSD and MCPD Enterprise. See his articles on simple-talk.


















<September 2007>
SuMoTuWeThFrSa
2627282930311
2345678
9101112131415
16171819202122
23242526272829
30123456
Raw Materials: Mirror, Mirror, on the Desk
 Seeing ourselves as we see ourselves. Read more...

Planning for Disaster
 There is a certain paradox in being advised to expect the unexpected, but the DBA must plan and prepare... Read more...

Transparent Data Encryption
  Transparent Data Encryption is designed to protect data by encrypting the physical files of the... Read more...

Software Piracy in Pakistan
 Alamzeb Khan, our Simple-Talk correspondent in Pakistan, goes undercover to discover the true scale of... Read more...

Implementing Cluster Continuous Replication, Part 3
 Cluster continuous replication (CCR) uses log shipping and failover to provide a more resilient email... Read more...