Click here to Skip to main content
13,197,548 members (51,507 online)
Click here to Skip to main content
Add your own
alternative version

Stats

2.1K views
2 bookmarked
Posted 11 Oct 2017

Create Big Sample Table by random function

Rate this:
Please Sign up or sign in to vote.
Sometime you need to have big table for testing performance optimization and indexing and partitioning and Etc. , so you can make it easily by using random function and a small table.

Introduction

sometime you need big table for testing performance optimization and indexing and partitioning and Etc. , so you can make it by using random function and small table.

you can use New() function in order by argument you can access a random record and use loop and make many 

records , but you will have many records with sames values in fields. so you need to use NewID() function for each column independently.

Let's do it.

Ingredients List :

Source  Database : AdventureWorks2012

Source Table :  HumanResources.Employee

Loop: for replicate random records

New_id: for random select value

Target Table : Employee_Sample

Source Code table Employee_Sample

CREATE TABLE Employee_Sample(
     BusinessEntityID         int  IDENTITY(1,1) NOT NULL,
     NationalIDNumber         nvarchar (15) NOT NULL,
     LoginID                  nvarchar (256) NOT NULL,
     OrganizationNode         hierarchyid    NULL,
     OrganizationLevel        smallint       NULL,
     JobTitle                 nvarchar(50) NOT NULL,
     BirthDate                date       NOT NULL,
     MaritalStatus            nchar (1)  NOT NULL,
     Gender                   nchar (1)  NOT NULL,
     HireDate                 date       NOT NULL,
     SalariedFlag             bit        NOT NULL,
     VacationHours            smallint   NOT NULL,
     SickLeaveHours           smallint   NOT NULL,
     CurrentFlag              bit        NOT NULL,
     rowguid                  uniqueidentifier  NOT NULL,
     ModifiedDate             datetime  NOT NULL,
 CONSTRAINT  PK_Sample  PRIMARY KEY NONCLUSTERED 
(
     BusinessEntityID  ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON  [PRIMARY] 
) ON  [PRIMARY] 
use AdventureWorks2012 
go
--Declare Parameters
Begin 

Declare @NationalIDNumber     nvarchar(15)     
Declare @LoginID              nvarchar(256)    
Declare @OrganizationNode     hierarchyid      
Declare @OrganizationLevel    smallint         
Declare @JobTitle             nvarchar(50)     
Declare @BirthDate            date             
Declare @MaritalStatus        nchar (1)        
Declare @Gender               nchar (1)        
------Random Date Parameters
Declare @HireDate             date             
Declare @FromHireDate         date
Declare @ToHireDate           date
-----
Declare @SalariedFlag         bit              
Declare @VacationHours        smallint         
Declare @SickLeaveHours       smallint         
Declare @CurrentFlag          bit              
Declare @rowguid              uniqueidentifier 
------Random Datetime Parameters
Declare @ModifiedDate        datetime 
DECLARE @FromModifiedDate    datetime 
DECLARE @ToModifiedDate      datetime 
----- 
Declare @count int = 0
End


 while @count < 2000000

Begin 
set @count = @count + 1
--Set Parameters
Begin 
;with a 
as 
(
select  distinct  NationalIDNumber   from AdventureWorks2012.HumanResources.Employee 
)
select top 1 @NationalIDNumber = NationalIDNumber  from a 
order by newid()

-->
;with b 
as 
(
select  distinct  LoginID   from AdventureWorks2012.HumanResources.Employee 
)
select top 1 @LoginID = LoginID  from b 
order by newid()
-->
;with c 
as 
(
select  distinct  OrganizationNode   from AdventureWorks2012.HumanResources.Employee 
)
select top 1 @OrganizationNode = OrganizationNode  from c 
order by newid()

-->
;with d
as 
(
select  distinct  OrganizationLevel   from AdventureWorks2012.HumanResources.Employee 
)
select top 1 @OrganizationLevel = OrganizationLevel  from d
order by newid()

-->
;with e
as 
(
select  distinct  JobTitle   from AdventureWorks2012.HumanResources.Employee 
)
select top 1 @JobTitle = JobTitle  from e
order by newid()


-->
;with f
as 
(
select  distinct  BirthDate   from AdventureWorks2012.HumanResources.Employee 
)
select top 1 @BirthDate = BirthDate  from f 
order by newid()

-->
;with g
as 
(
select  distinct  MaritalStatus   from AdventureWorks2012.HumanResources.Employee 
)
select top 1 @MaritalStatus = MaritalStatus  from g
order by newid()

-->
;with h
as 
(
select  distinct  Gender   from AdventureWorks2012.HumanResources.Employee 
)
select top 1 @Gender = Gender  from h 
order by newid()

-->
select @FromHiredate = min(HireDate), @ToHiredate = max(HireDate)  from AdventureWorks2012.HumanResources.Employee 
select  @HireDate  = dateadd(day, 
               rand(checksum(newid()))*(1+datediff(day, @FromHiredate, @ToHiredate)), 
               @FromHiredate)

-->
;with j 
as 
(
select  distinct  SalariedFlag   from AdventureWorks2012.HumanResources.Employee 
)
select top 1 @SalariedFlag = SalariedFlag  from j 
order by newid()
-->
;with k 
as 
(
select  distinct  VacationHours   from AdventureWorks2012.HumanResources.Employee 
)
select top 1 @VacationHours = VacationHours  from k
order by newid()

-->
;with l
as 
(
select  distinct  SickLeaveHours   from AdventureWorks2012.HumanResources.Employee 
)
select top 1 @SickLeaveHours = SickLeaveHours  from l 
order by newid()

-->
;with m
as 
(
select  distinct  CurrentFlag   from AdventureWorks2012.HumanResources.Employee 
)
select top 1 @CurrentFlag = CurrentFlag  from m 
order by newid()

-->
set @rowguid = newid()

-->

select @FromModifiedDate  = min(ModifiedDate), @ToModifiedDate =max(modifieddate)  from AdventureWorks2012.HumanResources.Employee 
select  @ModifiedDate = DATEADD(day, rand(checksum(newid()))*(1+datediff(day, @FromModifiedDate, @ToModifiedDate)),@FromModifiedDate)

-->
End         


 insert into x.dbo.hh(  
       NationalIDNumber 
      , LoginID 
      , OrganizationNode 
      , OrganizationLevel 
      , JobTitle 
      , BirthDate 
      , MaritalStatus 
      , Gender 
      , HireDate 
      , SalariedFlag 
      , VacationHours 
      , SickLeaveHours 
      , CurrentFlag 
      , rowguid 
      , ModifiedDate ) 
values(
  @NationalIDNumber           ,
  @LoginID                    ,
  @OrganizationNode           , 
  @OrganizationLevel          , 
  @JobTitle                   , 
  @BirthDate                  , 
  @MaritalStatus              , 
  @Gender                     ,
  @HireDate                   , 
  @SalariedFlag               , 
  @VacationHours              , 
  @SickLeaveHours             ,
  @CurrentFlag                ,
  @rowguid                    ,
  @ModifiedDate          
                 )      

End
 

You can to make range values by aggregate functions like (Min ,Max,...) that exist in column line below code:

 select @FromModifiedDate  = min(ModifiedDate), @ToModifiedDate =max(modifieddate)  from AdventureWorks2012.HumanResources.Employee 
select  @ModifiedDate = DATEADD(day, rand(checksum(newid()))*(1+datediff(day, @FromModifiedDate, @ToModifiedDate)),@FromModifiedDate)   

You can see result set in below picture:

Good Luck.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ehsan khosravi esfarjani
Database Developer
Iran (Islamic Republic of) Iran (Islamic Republic of)
This member doesn't quite have enough reputation to be able to display their biography and homepage.

You may also be interested in...

Pro
Pro

Comments and Discussions

 
QuestionTks Pin
Member 1346400813-Oct-17 9:30
groupMember 1346400813-Oct-17 9:30 
AnswerRe: Tks Pin
Ehsan khosravi esfarjani13-Oct-17 18:21
memberEhsan khosravi esfarjani13-Oct-17 18:21 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.171020.1 | Last Updated 12 Oct 2017
Article Copyright 2017 by Ehsan khosravi esfarjani
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid