title: Mysql Stored Procedure to copy records in a Parent/Child/Grandchild hierarchy
body: <p>I have 3 tables parent, child &amp; grandchild of the form:</p>
  
  <pre><code>+----------------+   +----------------+   +---------------------+
  | parent         |   | child          |   | grandchild          |
  +----------------+   +----------------+   +---------------------+
  | parent_id (PK) |   | child_id (PK)  |   | grandchild_id (PK)  |
  | parent_data    |   | child_data     |   | grandchild_data     |
  +----------------+   | parent_id (FK) |   | child_id (FK)       |
                       +----------------+   +---------------------+ 
  </code></pre>
  
  <p>PK = auto-incrementing primary key.<br>
  FK = foreign key.</p>
  
  <p>I want a stored procedure that can copy a record in the parent table and any associated records in the child and grandchild tables. I can get the parent and child data copied ok, its the grandchild table I'm struggling with. This is as far as I've got:</p>
  
  <pre><code>CREATE FUNCTION sf_copy_parent(p_parent_id INT) RETURNS INT
  BEGIN
      DECLARE new_parent_id INT;
  
      -- create new parent record
      INSERT INTO parent(parent_data)
          SELECT parent_data FROM parent
          WHERE parent_id=p_parent_id;
      SET new_parent_id=LAST_INSERT_ID();
  
      -- copy child records
      INSERT INTO child(child_data,parent_id)
          SELECT child_data,new_parent_id FROM child
          WHERE parent_id=p_parent_id;
  
      -- copy grandchild records ???
  
  
      -- return
      RETURN new_parent_id;
  END
  </code></pre>
  
  <p>I'm using Mysql5.5 if that's important.</p>