Data generation using sql query

• 
  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 March 06, 2023 08:09

  - 0 +
• 

  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?

  Jon_Kirkwood March 29, 2023 07:18

  - 0 +
• 
  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.

  
  
  
  

  JoeSuarez May 24, 2023 08:07

  - 0 +

Add comment

Please sign in to leave a comment.