SQL Data Generator –Specific Domains for a Column

I was mocking up some test data and wanted to limit my list of values to specific items. In this case, I was modeling the game, Rock Paper Scissors. In my modeling, I had a field for the value played by someone. In this case, I used a text field in a quick model, but I wanted only those three values in the list.

However I wanted a lot of data and used SQL Data Generator to put a few thousand rows in the table. In doing this, I needed to customize the pattern for this field.

TLDR; Use (Rock|Paper|Scissors)

My table was simple, a player, a game, the play, and a win. The schema looked like this:

2016-03-10 11_17_50-Start

In data generator, I selected a first name for the first column, and then noticed the default data generation preview.

2016-03-10 11_19_08-Store

Random text, which isn’t what I need. This comes from this mask.

2016-03-10 11_20_03-Store

This is a Regular Expression, and will use random letters to fill the field. What I want is specifics. To do this, I need to specify the values I want. If I replace the middle values with Rock, I get this:

2016-03-10 11_22_09-Store

Not really what I need. This is randomly choosing values. What I want is a literal, so I’ll change the brackets to parenthesis.

2016-03-10 11_22_52-Store

This gives me Rock in places, repeats or NULL in others.

To specify an OR, I use the | operator. This allows me to choose one of two options.

2016-03-10 11_23_12-Store

Or three

2016-03-10 11_24_05-Store

Removing the * removes the matching multiple times.

If I have a domain of specific values (say for a lookup table or limited entries), using a custom regular expression can allow you to generate useful, but specific, test data.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.