Tips to generate a large SQL Server table for testing

To generate a large table with millions of records, it’s possible to use the following script:

declare @RecordsNum int = 10000000-(select count(*) from Person)

declare @Count int = 0

set nocount on

Begin transaction

while(@Count < @RecordsNum)

begin

insert into MyTable(Name, Address, Age)

values(substring(Convert(varchar(255), NewID()), 0, 30),

substring(Convert(varchar(255), NewID()),0 ,30), RAND(100) * 100)

set @Count = @Count + 1

End

commit

select count(*) from MyTable

Some things to do to improve performance of the script:

  • Set NoCount to ON
  • enclose the while loop in a transaction explicitly

Ideas were found in this page: http://mitchelsellers.com/blogs/2008/09/12/creating-random-sql-server-test-data.aspx

Leave a Reply

Your email address will not be published. Required fields are marked *