Tuesday 23 March 2010

How to run a SQL batch multiple times

Today I came across one surprising use of GO statement in SQL Server which is solely used as batch terminator in SQL Server utilities such as SQL Server Management Studio etc. Though “GO” is not a Transact –SQL statement it can be used in multiple ways .One such example is to run a batch of statements as illustrated below

CREATE TABLE TestData ( Name VARCHAR(100), Age INT)
GO
Insert into TestData
SELECT 'John' ,28
GO 100


In the above example I have inserted 100 rows of test data by just specifying 100 next to the GO statement. All these days I have used the traditional while loop statement for doing this, now just specify the number next to GO statment the job is done. This feature is no where documented and supported only in SQL Server 2005 and greater versions

6 comments:

  1. Brilliant tip thanks.

    Looking in the Help for SQL Server 2008 it is documented under GO.

    ReplyDelete
  2. Can you please write a article for SQL Server 2008 Architure..

    ReplyDelete
  3. Hi,

    This syntax is supported for SQL Server 2008 also

    ReplyDelete