Showing posts from September, 2016

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)

List of First Names and Last Names for Test Data, Plus a Query to Generate a Random Full Name

I found this page to be very helpful when I was generating random names for test data: Naturally, I didn't read everything and just grabbed the two CSV files (one for first names, a second for last names). Next, I created two tables (FirstNames, LastNames) and imported the CSV files into their respective tables. The following query will assemble a Full Name from a randomly selected First Name and randomly selected Last Name: -- 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 @RandomFi