I've searched around for a solution to our problem without sucess so I'm posting my quiestion here. My apologies if it has already been asked and answered.

Our application needs to be able to work with a number of databases and we initally designed the database schema to be as simple as possible as a result. This works, however, we are now looking at the issue of speed and it has become obvious that we are going to have to resort to stored procedures for certain operations and therein lies the problem. We need to duplicate a single record in one table plus all its child and grandchild records.

Now the SQL Server version of this I found on StackOverflow and adapted to our needs, but our target and preferred database is firebird and I'm having a little difficulty in writing the stored procedure.

So, our tables have surrogate primary keys but no foreign key contraints and no triggers.

The task is to copy a single parent record selected by its primary key and to copy its chld and grandchild records with appropriate new primary and foreign keys.

For example,

Schema
Parents Table - ParentID, Name
Children Table - ChildID, ParentID, Name
GrandchildrenTable - GrandChildID, ChildID, Name

Data
Parents Table
1, Tom
2, Dick
3, Harriet

Children Table
1, 1, Georgette
2, 1, Peter
3, 2, Fred
4, 3, Emily
5, 3, Alice

Grandchildren Table
1, 1, Andrew
2, 1, Ruth
3, 2, Andreas
4, 3, Tina
5, 3, Ian
6, 4, Chris
7, 4, Laura
8, 4, Karl
9, 5, Matt

Calling the stored procedure passing 3 as the parent ID should add the following:

Parents Table
4, Harriet

Children Table
6, 4, Emily
7, 4, Alice

Grandchildren Table
10, 6, Chris
11, 6, Laura
12, 6, Karl
13, 7, Matt

Any suggestions on how this should be best achieved?