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