Thursday, December 9, 2010

Sql Server: The Magic of GO Command

GO is not a Transact-SQL statement; it’s just a command to send signal so to send the current batch  (T-SQL statements between two GO commands) of T-SQL statements to Sql Server instance.
Go is a useful command especially when you want to execute a batch multiple times. I like to use it to insert multiple dummy records in a table, with just ONE insert statement. Let’s create a temporary table for this purpose
USE AdventureWorks
GO -- FIRST BATCH

CREATE TABLE #tempTable
      (tempID UNIQUEIDENTIFIER,tempMonth INT, tempDateTime DATETIME )

GO -- SECOND BATCH
These batches will just execute these separate batches for once as default value for GO is one. Lets insert 100 records in our temporary table with just one insert statement.

INSERT INTO #tempTable (tempID, tempMonth, tempDateTime)
SELECT NEWID(),(CAST(100000*RAND() AS INT) % 12) + 1 ,GETDATE()

GO 100 -- THIRD BATCH (EXECUTE THIS BATCH 100 TIME)
“Go 100” will execute this insertion batch for hundred times, and exultantly 100 records will be inserted. That’s what I call it “Magic of GO”. Check this.
SELECT * FROM #tempTable
Don't forget to drop temporary table, as good practice.
DROP TABLE #tempTable
 

2 comments:

All suggestions are welcome