Options for Creating Mock Data

It’s easy enough to create a handful of records for testing in SQL Server. What if you want 100 rows or 1000 rows? What if you want data that looks more legitimate compared to gibberish? In this post, we’ll look at different ways to generate mock data.

Gibberish

Let’s look at a way to create gibberish mock data. There are cases when this is helpful and more than enough to get you through some testing.

In the example below, we can get random strings, dates, and numbers:

SELECT CHAR((ABS(CHECKSUM(NEWID())) % 26) + 65) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + 
		CHAR((ABS(CHECKSUM(NEWID())) % 26) + 65) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) + 
		CHAR((ABS(CHECKSUM(NEWID())) % 26) + 65) + CHAR((ABS(CHECKSUM(NEWID())) % 26) + 97) AS 'MockString', 
	DATEADD(MILLISECOND, (ABS(CHECKSUM(NEWID())) % 6000) * -1, 
		DATEADD(MINUTE, (ABS(CHECKSUM(NEWID())) % 1000000) * -1, GETDATE())) AS 'MockDate', 
	(ABS(CHECKSUM(NEWID()))) AS 'MockNumber';
GO

The 97 in the string is used to get lowercase letters and 65 is used for uppercase letters. With this method, you can setup a WHILE loop to add mock data to your test table.

GenerateData

One site you can use for generating data is the aptly named generatedata.com. This site can be used for multiple export types besides SQL. It is limited to 100 rows as a demo site.

We can setup our table name, data types, and examples of how we want the data to look. After configuring our export settings at the bottom, we can click the generate button to view our script:

Double check the data types to make sure they are appropriate to what you are testing. In this example we want a date but the generated script has Birthdate as VARCHAR(255). We can change VARCHAR(255) to DATE and run the script successfully.

Confirming our table data:

Mockaroo

One more site for generating data is mockaroo.com. Mockaroo allows up to 1000 rows with a free account and plenty of options for how you want your mock data. For example, searching for the “Name” type returns the following choices:

If we generally stick to the table example we’ve been using, we can setup our table:

And click Download Data to download a SQL file to use for creating the table and inserting data:

We can confirm a few example rows of our mock data:

If you need some mock data, these are a few quick ways that can help you out.

Thanks for reading!