{"id":165,"date":"2014-10-09T10:28:31","date_gmt":"2014-10-09T10:28:31","guid":{"rendered":"http:\/\/www.codereview.co\/?p=165"},"modified":"2014-10-09T10:28:31","modified_gmt":"2014-10-09T10:28:31","slug":"how-to-execute-a-tsql-batch-several-times-using-go","status":"publish","type":"post","link":"http:\/\/www.codereview.co\/index.php\/sql-server-tutorials\/how-to-execute-a-tsql-batch-several-times-using-go\/","title":{"rendered":"How to execute a TSQL batch several times using GO?"},"content":{"rendered":"<p><span style=\"color: #000000;\"><strong>Scenario proposal<\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\">Sometimes there is a need to execute the same command or set of commands over and over again. This may be to insert or update test data or it may be to put a load on your server for performance testing.\u00a0 Whatever the need the easiest way to do this is to setup a while loop and execute your code, but in SQL 2005 there is an even easier way to do this.<\/span><\/p>\n<p><span style=\"color: #000000;\"><strong>Solution<\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\">In both SQL Server 2000 and SQL Server 2005 the keyword GO tells SQL Server to execute the preceding code as one batch.\u00a0 In SQL Server 2005 you have the ability to add a number after the GO command to tell SQL Server how many times to execute the batch.\u00a0 So let&#8217;s take a look at a couple of examples:<\/span><\/p>\n<p><span style=\"color: #000000;\">Let&#8217;s say you want to create a test table and load it with 1000 records.\u00a0 You could issue the following command and it will run the same command 1000 times:<\/span><\/p>\n<table width=\"100%\">\n<tbody>\n<tr>\n<td><span style=\"color: #000000;\">CREATE\u00a0TABLE\u00a0dbo.TEST\u00a0(ID\u00a0INT\u00a0IDENTITY\u00a0(1,1),\u00a0ROWID\u00a0uniqueidentifier) GO INSERT\u00a0INTO\u00a0dbo.TEST\u00a0(ROWID)\u00a0VALUES\u00a0(NEWID())\u00a0 GO\u00a01000<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><span style=\"color: #000000;\">Here is another example that executes both INSERT statements 1000 times.\u00a0\u00a0 As you can see you can add more and more statements to the batch to be run the set number of times that is specified after the GO.<\/span><\/p>\n<table width=\"100%\">\n<tbody>\n<tr>\n<td><span style=\"color: #000000;\">CREATE\u00a0TABLE\u00a0dbo.TEST\u00a0(ID\u00a0INT\u00a0IDENTITY\u00a0(1,1),\u00a0ROWID\u00a0uniqueidentifier)\u00a0 CREATE\u00a0TABLE\u00a0dbo.TEST2\u00a0(ID\u00a0INT\u00a0IDENTITY\u00a0(1,1),\u00a0ROWID\u00a0uniqueidentifier)\u00a0 GO\u00a0 INSERT\u00a0INTO\u00a0dbo.TEST\u00a0(ROWID)\u00a0VALUES\u00a0(NEWID())\u00a0 INSERT\u00a0INTO\u00a0dbo.TEST2\u00a0(ROWID)\u00a0VALUES\u00a0(NEWID())\u00a0 GO\u00a01000<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><span style=\"color: #000000;\">To do something similar to this in SQL Server 2000 you would need to write code such as the following. It is not that big a deal, but writing GO 1000 seems a bit easier to me.<\/span><\/p>\n<table width=\"100%\">\n<tbody>\n<tr>\n<td><span style=\"color: #000000;\">CREATE\u00a0TABLE\u00a0dbo.TEST\u00a0(ID\u00a0INT\u00a0IDENTITY\u00a0(1,1),\u00a0ROWID\u00a0uniqueidentifier) CREATE\u00a0TABLE\u00a0dbo.TEST2\u00a0(ID\u00a0INT\u00a0IDENTITY\u00a0(1,1),\u00a0ROWID\u00a0uniqueidentifier) GO DECLARE\u00a0@counter\u00a0INT\u00a0 SET\u00a0@counter\u00a0=\u00a00\u00a0 WHILE\u00a0@counter\u00a0&lt;\u00a01000\u00a0 \u00a0\u00a0BEGIN \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INSERT\u00a0INTO\u00a0dbo.TEST\u00a0(ROWID)\u00a0VALUES\u00a0(NEWID()) \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INSERT\u00a0INTO\u00a0dbo.TEST2\u00a0(ROWID)\u00a0VALUES\u00a0(NEWID()) \u00a0\u00a0SET\u00a0@counter\u00a0=\u00a0@counter\u00a0+\u00a01 \u00a0\u00a0END<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><span style=\"color: #000000;\"><strong>Thanks for reading this article,<\/strong><\/span><\/p>\n<p><span style=\"color: #000000;\"><strong>Next steps :<\/strong><\/span><\/p>\n<ol>\n<li><span style=\"color: #000000;\"><strong> Add this script to your database toolkit<\/strong><\/span><\/li>\n<li><span style=\"color: #000000;\"><strong> Share this with your colleagues because Sharing is Learning<\/strong><\/span><\/li>\n<li><span style=\"color: #000000;\"><strong> Comment below if you need any assistance<\/strong><\/span><\/li>\n<\/ol>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Scenario proposal Sometimes there is a need to execute the same command or set of commands over and over again. This may be to insert or update test data or it may be to put a load on your server for performance testing.\u00a0 Whatever the need the easiest way to do this is to setup [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[32,3],"tags":[57,58,13,12,56],"_links":{"self":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/165"}],"collection":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/comments?post=165"}],"version-history":[{"count":1,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/165\/revisions"}],"predecessor-version":[{"id":166,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/165\/revisions\/166"}],"wp:attachment":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/media?parent=165"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/categories?post=165"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/tags?post=165"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}