Friday, April 19, 2024
HomeHow ToGenerating Related Data Elements with SQL Data Generator

Generating Related Data Elements with SQL Data Generator

Recently we got an interesting question from our user:

The docs don’t address directly, but how can we generate three related data elements, like dateof birth, age, and date of death for instance, in one script efficiently as it generates test rows/records and then populate the fields in a test database table? If docs do address this, please point me to where, if you would… Thanks!

Another user has posted a similar question on the Devart support forum.

We added the new “Death Date” generator to help resolve the problem. dbForge Data Generator for SQL Server is supplied with a wide collection of generator templates that can be easily used for the creation of your own data generators. Actually, it is the most “sad” generator on our list. Let’s have a look at how to use it.

Here is a demo table that contains the PersonAge, DateofBirth, and DateofDeath fields.

Let’s start dbForge Data Generator for SQL Server and populate the table with test data.

We see a warning that states that the global name “DOB” is not defined. DOB is the column that contains dates of birth. It must be located in the same table.

Select the DateofDeath column to see the details. As you can see, the Death Date generator is automatically selected and mapped to the column.

The following step is to modify the python script. Find the bd = DOB string. It is required to replace the DOB placeholder with the actual column that contains dates of birth (DateofBirth in our case). Note, it is case sensitive. Thus, in this particular example, we need to write bd = DateofBirth

Once you have modified the python script with the correct column name, another warning might appear in the DateofDeath column (String was not recognized as a valid DateTime).

The reason is that we have a different column data format by default.

In that case, the last step is to replace “/” with the “.”

To generate the correct Person age date, that corresponds to DateofBirth and DateofDeath, select the PersonAge column and specify appropriate columns instead of placeholders. Also, keep in mind that the date format may require some modifications.

Here is the result:

This way you can generate related data elements. The python script is well commented and you can modify it to use for your own purposes.

Andrey Langovoy
Andrey Langovoy
Product manager at Devart