Today I needed to generate a lot of test data and my SQL-fu hadn’t been fed properly lately so I wasn’t sure what approach to take. Luckily I only needed pretty basic arbitrary data, but lots of it. The easiest way was to create a easily configurable script that I could run whenever I needed to create more of my data.
Below is a stripped down example of what such a script could look like. The point of this script is to generate a whole bunch of Person data:
CREATE TABLE [dbo].[Person]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](100) NOT NULL, [Age] [int] NOT NULL, [Email] [varchar](255) NULL, CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
Script to generate X number of people:
DECLARE @CurrentRow INT DECLARE @CurrentAge INT DECLARE @PeopleCount INT DECLARE @AgeMin INT DECLARE @AgeMax INT DECLARE @Name VARCHAR(100) --CONFIGURATION SET @PeopleCount = 10 --The number of people to generate SET @Name = 'TEST_' --The name of the person (will have an incremented number as suffix) SET @AgeMin = 18 --The minimum age of the people to generate SET @AgeMax = 58 --The maximum age of the people to generate ---------------- SET @CurrentRow = 0 SET @CurrentAge = @AgeMin SET NOCOUNT ON WHILE @CurrentRow < @PeopleCount BEGIN SET @CurrentRow = @CurrentRow + 1 SET @CurrentAge = @CurrentAge + @CurrentRow IF @CurrentAge > @AgeMax BEGIN SET @CurrentAge = @AgeMin END INSERT INTO Person ( Name, Age ) VALUES ( @Name + CONVERT(VARCHAR, @CurrentRow), @CurrentAge ) END SET NOCOUNT OFF PRINT CONVERT(VARCHAR, @PeopleCount) + ' people created!'