Data generation using sql query

he_man

I have 3 tables-person_id table with id column,  person table with columns - name and id and person_details table with columns name,id and otherDetails where id will be filled from person_id table. I will generate the person_id table first, then take the id from the same table to fill details in the person table.  but I want the person_details table column - name to be filled with the names from person table. I tried with SQL query such as SELECT person.name from person where person.id = id. But it is not working. Can anyone help?

Jon_Kirkwood

Hi @he_man

Thank you for reaching out on the Redgate forums regarding your Data Generator query.
I don't believe the select statement would possibly work directly in this scenario as the data in the person_id would already have been generated as new data and wouldn't necessarily match the person & person_details table. 

Fairly sure this can be done through the use of secondary key settings in Data Generator. I'm attempting to spin up an example of your scenario to test generating the data as you outline and will update this forum again shortly with another post.

Jon_Kirkwood

I created an example SQL Data Generator project using your three tables.

I used the following settings and believe I have created a data set along the lines of what you are after

Using foreign key generators linked to the person_id table kept all values the same. Then I used a SQL statement on person_details to sync the names between person & person_details

 

• Table - person_id

id - 5 digit ID (set unique, no null values)

 

• Table - person

id - foreign key (person_id.id) - all key values, don't shuffle

name - standard generator (First name, no nulls)

 

• Table person_details

id - foreign key (person_id.id) - all key values, don't shuffle

name - SQL Statement (select name from person where id = id)

otherDetails - standard generator (regex, allow nulls)

 

 

 

Is this the type of output you are looking to generate?

JoeSuarez

INSERT INTO person_details (name, id, otherDetails)
SELECT person.name, person.id, 'someDetails'
FROM person
INNER JOIN person_id ON person.id = person_id.id;

In the above query, we use the INSERT INTO statement to insert data into the "person_details" table. We select the "name" column from the "person" table, along with the corresponding "id" column and a placeholder value for "otherDetails". We perform an INNER JOIN on the "id" column between the "person" and "person_id" tables to ensure that we only retrieve the records with matching IDs.

Make sure to replace 'someDetails' with the appropriate value or column from your original data source that contains the additional details for each person.

By executing this query, you should be able to populate the "name" column in the "person_details" table with the names from the "person" table based on matching IDs and ensure a more qualitative CRM data enrichment.