Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Generating Related Data Elements

DZone's Guide to

Generating Related Data Elements

This article shows you how to generate related data elements with dbForge Data Generator for SQL Server.

· Database Zone
Free Resource

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

Recently we got an interesting question from a user:

The docs don’t address directly, but how can we generate three related data elements, like date of 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 the similar question on the Devart support forum.

We added the new "Death Date" generator to help resolve the problem. Actually, it is the "saddest" generator in 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.

 
dod_table  

 



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

dod_start_data_gen

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.

dod_default_python1

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

dod_modified_python1

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

dod_default_date_format

So, the last step is to replace "/" with the "."... once it is done, the required data is generated.

dod_result

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.

dod_actual_person_age

Here is the result:

DoD_result_page

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.

This article was originally written by Andrey Langovoy.

It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

Topics:
sql server ,database design ,database management ,sql databases ,database administration ,data generation ,test data

Published at DZone with permission of Jordan Sanders. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}