Parent Child Script to Generate Test Data

• rjjh78

  SSCrazy

  Points: 2784

  More actions

  July 24, 2019 at 9:41 pm

  #3665898

  Hello,  I am working to create a script because I want to create test records for Parent and Child database tables.  I have create a Parent table which defines how many records for each Parent should go into the Child table.  My challenge is I cannot seem to figure out how to create the desired rows into the Child table so I do not need to create each row manually.  I have created the following sample code in hopes to explain how/what I am wanting to do.

   

  Can someone explain or help me create the logic to take the NoOfChildrenRecs field in the Parent table, and auto-generate the rows into the Child table?

   

  -- Parent Table
  -- DROP TABLE #Parent
  CREATE TABLE #Parent (PID int IDENTITY(1,1), PName varchar(10), NoOfChildrenRecs int)
  INSERT INTO  #Parent (PName, NoOfChildrenRecs) VALUES ('Bob', 1)
  INSERT INTO  #Parent (PName, NoOfChildrenRecs) VALUES ('John', 2)
  INSERT INTO  #Parent (PName, NoOfChildrenRecs) VALUES ('Jan', 2)
  INSERT INTO  #Parent (PName, NoOfChildrenRecs) VALUES ('Jill', 3)
  INSERT INTO  #Parent (PName, NoOfChildrenRecs) VALUES ('Kim', 1)
  
  SELECT * FROM #Parent
  
  -- Child Table
  CREATE TABLE #Child (CID int IDENTITY(1,1), PID int, PName varchar(1), CName varchar(10))
  -- I would like to Insert 1 row per child based on referecning the NoofChildrenRecs in the #Parent table
  
  -- Desired Results
  -- CIDPIDPNameChildName
  -- 1 1 Bob
  -- 2 2 John
  -- 3 2 John
  -- 4 3 Jan
  -- 5 3 Jan
  -- 6 4 Jill
  -- 7 4 Jill
  -- 8 4 Jill
  -- 9 1 Kim

• drew.allen

  SSC Guru

  Points: 76948

  More actions

  July 24, 2019 at 10:30 pm

  #3665904

  Use a tally table.  Here I've used an inline tally table.

  INSERT #Child (PID, PName)
  SELECT
  p.PID
  ,p.PName
  FROM #Parent p
  CROSS APPLY (VALUES(1), (2), (3), (4)) t(n)
  WHERE p.NoOfChildrenRecs >= t.n

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA