Sample Test Data Generation using SQL Script.

Creating Sample Test Data using batch execution and looping.

Problem: Sometimes we need to have sample data generating for testing purposes or for checking the execution times.

Solution: Using Batch Execution GO (Transact-SQL), we are looping through the number of times to get the required number of rows for our purpose.

Below is the example of how to create a table and loop through some random area codes for populating the unique phone numbers by using CHECKSUM.

CREATE TABLE dbo.Employees

(

                EmployeeID int identity (1, 1),

                Name varchar(32),

                PhoneNumber int

)

go

set nocount on

--adding 100 employees with random area codes

insert into dbo.Employees([Name]
      ,[PhoneNumber])

select

                substring(cast(newid() as varchar(68)), 1, 6) as Name

       , (

             SELECT TOP (1) cast (c1 as int)

             FROM (

             VALUES ('123'), ('234'), ('345'), ('456'), ('678')

             ) AS T1(c1)

             ORDER BY ABS(CHECKSUM(NEWID()))

       ) as Ph

GO 100 – change this number to whatever you want.

select * from dbo.employees