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