A Script A Day - Day 13 - Chris' Top Tip - Batches

Today’s post is pretty cool.  So you have been reading up about something on the World Wide Web or you want to test something that has come to light, you create a table and want to insert a million records.  In the past I imagine some of you have used a cursor or while loop like I have, that is until I came across this.

You can specify how many executions of a batch SSMS will run by passing a number after the batch separator.  So in the example below 1 million records will be inserted into the TopTip Table!  Now SSMS highlights the 1000000 as a syntax error but it does work. 

Go on give it a try!

/*

      -----------------------------------------------------------------

      Chris’ Top Tip - Batches

      -----------------------------------------------------------------

     

      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.

      You may republish altered code as long as you give due credit.

      You must obtain prior permission before blogging this code.

 

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

     

      -----------------------------------------------------------------

*/

-- Change database context

USE DatabaseNameHere;

GO

-- Supress messages

SET NOCOUNT ON

-- Create test table

IF NOT EXISTS(SELECT 1 FROM sys.tables where name = 'TopTip')

CREATE TABLE TopTip

(

      TopTipID INT IDENTITY (1,1),

      Col1 VARCHAR(100),

      Col2 VARCHAR(100),

      Col3 VARCHAR(100),

      Col4 VARCHAR (100),

);

GO

-- Now this is the cool bit!!!

INSERT INTO TopTip (Col1,Col2,Col3,Col4)

VALUES ('This', 'Is', 'Cool!','asaADSFHKUASASDFJ9IEW8R934HR4C398T0WMCRWPEOKCPMO3R');

GO 1000000

Enjoy!

Chris