Write a TSQL Script to insert 1Lac(1,00,000) arbitrary records into a table

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 149
Let's say we have a table as under

DECLARE @t TABLE(EmployeeName VARCHAR(50),EmailAddress VARCHAR(50),PhoneNumber VARCHAR(50))


Objective is to insert 1Lac(1,00,000) random records into the table using TSQL Script. The below program will do so

--Table definition
DECLARE @t TABLE(EmployeeName VARCHAR(50),EmailAddress VARCHAR(50),PhoneNumber VARCHAR(50))

--generate a number table with 1 Lac (100000) rows
;with cte as(
select rn=1
union all
select rn=rn+1 from cte where rn<100000 --1 Lac
)
-- Insert records into @t
INSERT INTO @t
select
EmployeeName = 'Name'+ cast(rn as varchar(10))
,EmailAddress = 'Email'+ cast(rn as varchar(10))+'@test.com'
,PhoneNumber = ABS(CHECKSUM(NEWID()))
from cte
option (maxrecursion 0)

--Project the record
Select *
From @t


At first we have generated a number table with 1 Lac (100000) rows using recursive CTE. Next we have inserted some arbitary records into the @t table. And finally we have projected the records

Comments or Responses

Login to post response