How to generate SQL Linked List Data with Python Generator

In this post, we would like to show how to quickly generate table values that are based on the values of another column of same table. For this purpose, we will use the Python Generator being a part of dbForge Data Generator for SQL Server.

First, let’s create a test table using the following script:

Then, we run the Data Generation wizard, select the Python generator for the previous_id column and enter the following script:

previous_id-linked-list

For the next_id column, we also select the Python generator and enter the following script:

next_id-linked-list

Voila! As a result of the above manipulations, we’ve got the records, where each record references to a next record using the next_id column:

result-linked-list

result2-linked-list

That was a good start, but let’s complicate the task a bit, and try generating several linked lists of various sizes, whereas each list must be related to a unique Parent. And furthermore, the value in each child must be computed from a value column in its Parent.

So again, let’s create two tables:

Then, we run the Data Generation wizard and clear the Include NULL values option.

Let’s enter the following Python codes:

  1. For the previous_id column:
  2. For the next_id column:
  3. For the commonParent table:

After that, we generate a script and run it against the database.

To remove the records from the Parent table which didn’t have corresponding records in the TestLinkedListTableChild table, we run the following script:

Since the last chain couldn’t be not closed,
unclosed-chain-linked-list

let’s run the following script to remove the last broken record:

Finally, we’ve got the desired result – totally valid chains:

result-2-linked-list

Leave a Comment