SQL Query to Populate a Table with Random Data

My go to database for trying out new ideas is my CrittersAndThings database. Below is the layout of the first version from years ago. I use it even today when I need something simple to bang on. (The later versions get pretty crazy and sometimes it's a bit much for testing simple things.)

The general idea is that the world (my test world, that is) has:

• critters -- living animal-like things such as people, orcs, fish, birds, bugs, sentient gelatinous masses, you name it
• things -- all the stuff that isn't a critter, such as plants, minerals, planets, buckets of mop water, clouds of gas, etc.

Database Structure

Table: Critters

We store critters here.

• CritterID (PK, int, not null)
• Name (varchar(50), null)
• Height (int null)
• Weight (int null)
• Age (int null)
• CritterTypeID (FK, int, null)
• IsAlive (bit, null)

Table: CritterTypes

This table contains various types of critters.

• CritterTypeID (PK, int, not null)
• Name (varchar(50), null)
• Description (varchar(50), null)
• PrimaryDomainTypeID (FK, int null)

My CritterTypes table contains the following rows:

CritterTypeID	Name	Description	PrimaryDomainTypeID
1	Bipedal	Walks	1
2	Fish-like	Swims	2
3	Bird-like	Flies	3

Table: DomainTypes

A domain is a a realm in which a Critter (or thing) exists. For instance, "Land" is a domain; "Sky" is a domain; "Water" is a domain.

• DomainTypeID (PK, int, not null)
• Name (varchar(50), null)
• Description (varchar(50), null)

Populating the Database

So, once we have these tables set up, then we can populate them. It's easy enough to populate CritterTypes and DomainTypes tables.

Populating the Critters table, though, is another matter. However, I use the following script to do the job. Note that you use the variable @NumberOfCritters to control how many rows you want to put in the table.

declare @seed int;
declare @string varchar(256);

select @seed = 1234; -- saved start seed

exec usp_generateIdentifier
    @seed = @seed output
    , @string = @string output;
print @string;  
exec usp_generateIdentifier 
    @seed = @seed output
    , @string = @string output;
print @string;  
exec usp_generateIdentifier 
    @seed = @seed output
    , @string = @string output;
print @string; 

/****************************************************/
/** Delete existing critters                        */
/****************************************************/
DELETE FROM
CrittersAndThings.dbo.Critters
GO

/****************************************************/
/** Reset identity column                           */
/****************************************************/
DBCC CHECKIDENT ('[Critters]', RESEED, 0);
GO

/****************************************************/
/** Randomly generate critters                      */
/****************************************************/
DECLARE @NumberOfCritters int;
SET @NumberOfCritters = 1000000;

DECLARE @Counter int;
SET @Counter = 1;

DECLARE @seed int;
SELECT @seed = 1234; -- saved start seed

WHILE (@Counter <= @NumberOfCritters)
BEGIN
-- Generate random name
DECLARE @RandomName varchar(50);

-- METHOD #1
--DECLARE @string varchar(256);
--EXEC usp_generateIdentifier @seed = @seed output, @string = @string output;
--SET @RandomName = @string;

-- METHOD #2
-- Generate random LastNameID
DECLARE @RandomLastNameID int;
DECLARE @UpperLastNameID int;
DECLARE @LowerLastNameID int;
SET @UpperLastNameID = 88799;
SET @LowerLastNameID = 1;
SET @RandomLastNameID = RAND() * @UpperLastNameID + @LowerLastNameID;

-- Generate random FirstNameID
DECLARE @RandomFirstNameID int;
DECLARE @UpperFirstNameID int;
DECLARE @LowerFirstNameID int;
SET @UpperFirstNameID = 5494;
SET @LowerFirstNameID = 1;
SET @RandomFirstNameID = RAND() * @UpperFirstNameID + @LowerFirstNameID;

-- Generate FullName from random first & last name IDs
SET @RandomName = (
SELECT
(SELECT
FirstNames.Name AS FName
FROM
FirstNames
WHERE
FirstNames.FirstNameID = @RandomFirstNameID)
+ ' ' + 
(SELECT
LastNames.Name AS LName
FROM
LastNames
WHERE
LastNames.LastNameID = @RandomLastNameID) AS FullName
)
PRINT @RandomName

-- Generate random Height
DECLARE @RandomHeight int;
DECLARE @UpperHeight int;
DECLARE @LowerHeight int;
SET @UpperHeight = 100;
SET @LowerHeight = 1;
SET @RandomHeight = RAND() * @UpperHeight + @LowerHeight;

-- Generate random Weight
DECLARE @RandomWeight int;
DECLARE @UpperWeight int;
DECLARE @LowerWeight int;
SET @UpperWeight = 10;
SET @LowerWeight = 1;
SET @RandomWeight = RAND() * @UpperWeight + @LowerWeight;

-- Generate random Age
DECLARE @RandomAge int;
DECLARE @UpperAge int;
DECLARE @LowerAge int;
SET @UpperAge = 100;
SET @LowerAge = 1;
SET @RandomAge = RAND() * @UpperAge + @LowerAge;

-- Generate random CritterTypeID
DECLARE @RandomCritterType int;
DECLARE @UpperCritterType int;
DECLARE @LowerCritterType int;
SET @UpperCritterType = 3;
SET @LowerCritterType = 1;
SET @RandomCritterType = RAND() * @UpperCritterType + @LowerCritterType;

INSERT INTO Critters (
Name,
Height,
Weight,
Age,
CritterTypeID,
IsAlive
)
VALUES (
@RandomName,
@RandomHeight,
@RandomWeight,
@RandomAge,
@RandomCritterType,
1
);
SET @Counter = @Counter + 1;
END

Note that the random names are drawn from two additional tables (FirstNames, LastNames) described in the earlier post, "List of First Names and Last Names for Test Data, Plus a Query to Generate a Random Full Name."

Depending on how many critters I'm generating, this script runs anywhere from a few seconds to 5 minutes or more. Once done, though, the script has populated my test world with plenty of randomly created Critters.