Creating Meaningful Test Data
Language(s):SQL Server
Category(s):Generating Test Data
Whether you are a developer or work in QA, you have probably encountered the problem of a blank database that needs to be seeded for development or testing. This article will show you an easy way to use the SQL Server RAND function to generate a table of names that looks real but isn't.

-- =============================================

-- Author:          Jon Vote

-- Create date: 2015-01-11

-- Description:     Create Names

-- =============================================

Create PROCEDURE [dbo].[CreateNames]

       @NumRecords int = 1000

AS

BEGIN

       SET NOCOUNT ON;

       Declare @NumFirstNames int = (Select Max(Id) from FirstNames);

       Declare @FirstNameId int;

       Declare @FirstName nvarchar(50);

      

       Declare @Gender char;

       Declare @NumLastNames int = (Select MAX(Id) from LastNames);

       Declare @LastNameId int;

       Declare @LastName nvarchar(50);

       Declare @MiddleInitial varchar(1);

      

       Declare @DOB Date;

       Declare @NumDays int;

      

       Declare @SSN varchar(11);

       Declare @Count int = @NumRecords;

       while @Count > 0 begin

             set @FirstNameId = RAND() * @NumFirstNames + 1;

             set @LastNameId = RAND() * @NumLastNames + 1;

             Select @FirstName   =      FirstName,

                           @Gender =           Gender

             From   FirstNames

             Where  Id = @FirstNameId;

             Select @LastName = LastName

             From   LastNames

             Where  Id = @LastNameId;

             Declare @Matches int = 1;

             While @Matches > 0 begin

                    Set @SSN = convert(varchar, convert(int, RAND() * 10))

                                  + convert(varchar, convert(int, RAND() * 10))

                                  + convert(varchar, convert(int, RAND() * 10))

                                  + '-'

                                  + convert(varchar, convert(int, RAND() * 10))

                                  + convert(varchar, convert(int, RAND() * 10))

                                  + '-'

                                  + convert(varchar, convert(int, RAND() * 10))

                                  + convert(varchar, convert(int, RAND() * 10))

                                  + convert(varchar, convert(int, RAND() * 10))

                                  + convert(varchar, convert(int, RAND() * 10));

                    Set @Matches = (Select Count(*) From Names where SSN = @SSN);

             end

             Set @NumDays = RAND() * 50 * 365;

             Set @DOB = DateAdd(Day, @NumDays, '1950-01-01');

            

             Set @MiddleInitial = Char(RAND() * 26 + ASCII('A'));

             Insert Into         Names

                                        (

                                               FirstName,

                                               LastName,

                                               MiddleInitial,

                                               Gender,

                                               DOB,

                                               SSN

                                        )

             Values              (

                                               @FirstName,

                                               @LastName,

                                               @MiddleInitial,

                                               @Gender,

                                               @DOB,

                                               @SSN

                                        );

             Set @Count = @Count - 1;

       end;

END

This article has been viewed 3870 times.
The examples on this page are presented "as is". They may be used in code as long as credit is given to the original author. Contents of this page may not be reproduced or published in any other manner what so ever without written permission from Idioma Software Inc.