How To Generate and Fill a Doubly Linked List Table

May 27th, 2019

Introduction

In this article, we’ll learn how to fill a doubly linked list table with SQL test data. In such tables, there can be various independent chains unified by unique tags into groups.

You can see an example of a doubly linked list table below:

Doubly Linked List Example

Of course, to fill such a table with data, you could write a rather simple SQL script utilizing the WHILE statement. However, this method has some disadvantages. First of all, the data created in this way will look very unrealistic. In addition, a doubly linked list table may be connected with other tables (i.e., it may have foreign keys), which makes filling it with data much more difficult.

This is why we’ll use dbForge Data Generator for SQL Server to generate data for our table. For the sake of this example, we’ll create one doubly linked list table with no foreign keys.

The process

Here are the steps we will need to take:

1. Run dbForge Data Generator.

2. Create a new database called TestDB and, inside of it, a new table.

Table Creation Script

In this table, the chainGroup field specifies the chain group value, previous_id points to the previous element in the chain, and next_id points to the chain’s next element.

3. Click New Data Generation on the database. In the Data Generation Wizard, you can leave all options at their default values.

4. For the previous_id, next_id and chainGroup columns, we will need to uncheck the Include NULL option.

Include NULL Option

5. Next, select the Python generator for these columns:

Python Generator Option

6. For the previous_id column, we’ll need to enter the following script in the Python editor:

This is the result we’ll get:

Generation Options Result

7. Next, for the next_id column, enter the following script in the Python editor:

8. For the chainGroup column, use the following script:
9. As a result, we’ll get a Data Preview like the one seen on the following screenshot. That’s it!

Resulting Table

10. For this particular example, the largest possible chain length was set to 8. However, you can set this value according to your needs. To do this, you would need to make changes in all three columns – previous_id, next_id and chainGroup.

Max Chain Length Setup

Summary

In this article, we looked at a way of filling a doubly linked list table with randomly generated SQL test data. By configuring the Python generator, you can generate data for tables based on other database structures.

Leave a Comment