SQL Server: generate test data in two related tables
Create two tables linked by a foreign key and fill them with random data, for performance tests.
This script creates two tables linked by a foreign key (tblAuthors and tblBooks), inserts 12,000 authors, then 20,000 books whose author, price and edition are drawn at random. It is used to produce a volume of data for performance tests.
Query
CREATE Table tblAuthors
(
Id int identity primary key,
Author_name nvarchar(50),
country nvarchar(50)
)
CREATE Table tblBooks
(
Id int identity primary key,
Auhthor_id int foreign key references tblAuthors(Id),
Price int,
Edition int
)
Declare @Id int
Set @Id = 1
While @Id <= 12000
Begin
Insert Into tblAuthors values ('Author - ' + CAST(@Id as nvarchar(10)),
'Country - ' + CAST(@Id as nvarchar(10)) + ' name')
Print @Id
Set @Id = @Id + 1
End
Declare @RandomAuthorId int
Declare @RandomPrice int
Declare @RandomEdition int
Declare @LowerLimitForAuthorId int
Declare @UpperLimitForAuthorId int
Set @LowerLimitForAuthorId = 1
Set @UpperLimitForAuthorId = 12000
Declare @LowerLimitForPrice int
Declare @UpperLimitForPrice int
Set @LowerLimitForPrice = 50
Set @UpperLimitForPrice = 100
Declare @LowerLimitForEdition int
Declare @UpperLimitForEdition int
Set @LowerLimitForEdition = 1
Set @UpperLimitForEdition = 10
Declare @count int
Set @count = 1
While @count <= 20000
Begin
Select @RandomAuthorId = Round(((@UpperLimitForAuthorId - @LowerLimitForAuthorId) * Rand()) + @LowerLimitForAuthorId, 0)
Select @RandomPrice = Round(((@UpperLimitForPrice - @LowerLimitForPrice) * Rand()) + @LowerLimitForPrice, 0)
Select @RandomEdition = Round(((@UpperLimitForEdition - @LowerLimitForEdition) * Rand()) + @LowerLimitForEdition, 0)
Insert Into tblBooks values (@RandomAuthorId, @RandomPrice, @RandomEdition)
Print @count
Set @count = @count + 1
End
Notes
- The script creates the tables without dropping them first: run it in a test database where these table names do not exist.
- Rows are inserted one at a time in a loop, so the run time grows with the volume.