0

I have a table with approximately 2 million records in old_parent that I need to split into the table structure below it.

enter image description here

I've tried using something like a CTE using a method I saw here.

Example:

WITH address_ins AS (
  INSERT INTO address (address)
  VALUES op.address
  RETURNING id
  )
, parent_ins AS (
  INSERT INTO parent (info, parent_address_id)
  VALUES (op.info, address_ins.id)
, child_1_ins AS (
  INSERT INTO child_1 (thing_1, parent_id)
  VALUES (op.thing_1, parent_ins.parent_id)
)
  ... So On So Forth
SELECT * FROM old_parent op;

But this method does not work because the statements don't recognize op. Am I just missing something small or is there a better way to do this?

Matt Weber
  • 2,808
  • 2
  • 14
  • 30

1 Answers1

1

I think this is what you're looking for:

WITH ins1 AS (
   INSERT INTO parent_address (address,old_parent_id)
   SELECT address,id FROM old_parent
   RETURNING *
   )
, ins2 AS (
   INSERT INTO parent (info, parent_address_id)
   SELECT op.info,ins1.id
   FROM   ins1 LEFT OUTER JOIN old_parent op ON op.id = ins1.old_parent_id
   RETURNING *
   )
, ins3 AS (
   INSERT INTO child_1 (thing_1, parent_id)
   SELECT op.thing_1,ins2.id
   FROM   ins2 
    LEFT OUTER JOIN ins1 ON ins1.id = ins2.parent_address_id
    LEFT OUTER JOIN old_parent op ON op.id = ins1.old_parent_id
   RETURNING *
   )   
, ins4 AS (
   INSERT INTO child_2 (thing_2, parent_id)
   SELECT op.thing_2,ins2.id
   FROM   ins2 
    LEFT OUTER JOIN ins1 ON ins1.id = ins2.parent_address_id
    LEFT OUTER JOIN old_parent op ON op.id = ins1.old_parent_id
   )   
, ins5 AS (
   INSERT INTO child_attribute (child_1_id,thing)
   SELECT ins3.id,op.thing
   FROM   ins3
    LEFT OUTER JOIN ins2 ON ins2.id = ins3.parent_id
    LEFT OUTER JOIN ins1 ON ins1.id = ins2.parent_address_id
    LEFT OUTER JOIN old_parent op ON op.id = ins1.old_parent_id
   )      
INSERT INTO other_child_attribute (child_1_id, other_thing)
SELECT ins3.id,op.other_thing
FROM   ins3
    LEFT OUTER JOIN ins2 ON ins2.id = ins3.parent_id
    LEFT OUTER JOIN ins1 ON ins1.id = ins2.parent_address_id
    LEFT OUTER JOIN old_parent op ON op.id = ins1.old_parent_id
VBStarr
  • 586
  • 6
  • 17