--- In firebird-support@yahoogroups.com, "kramnetsua" wrote:

>
> 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?

Since no-one has answered, I'll give it at try. Below you'll find approximately how I think it can be solved, I have no clue whether it is the 'best' solution or not.

CREATE PROCEDURE DUPLICATE(FromID Integer) AS
declare variable PID integer;
declare variable CIDOLD integer;
declare variable CID integer;
begin
PID = GenID(ParentIDGenerator, 1);
INSERT INTO Parents (ParentID, Name)
SELECT :PID, Name
FROM Parents
WHERE ParentID = FromID;
FOR SELECT ChildID FROM Children INTO :CIDOLD DO
BEGIN
CID = GenID(ChildrenIDGenerator, 1);
INSERT INTO Children(ChildID, ParentID, Name)
SELECT :CID, :PID, Name
FROM Children
WHERE ParentID = :FromID;
INSERT INTO GrandChildren(ChildID, Name)
SELECT :CID, Name
FROM GrandChildren
WHERE ChildrenID = :CIDOLD;
END
end

I've assumed you will create a BEFORE INSERT trigger to populate the GrandChildID column in case it is not part of the INSERT statement. If you don't do this you'll have to use a FOR SELECT for the GrandChildren table as well and I assume that to be a bit slower (though that's just a guess, I've no clue about performance of INSERT statements in a stored procedure.

HTH,
Set