Steve,
One approach ... I've just finished writing & testing
a similar situation (in FB2.0)!

The master table uses a BEFORE trigger with a generator
to load the primary key field.
A stored procedure, with input parameters for all the fields
of both the master & the detail tables, first does an
'insert ... returning (key field)' into the master table
and then, using that returned key field, does an insert
into the detail table. This stored procedure, which can be
called from either iSQL (via 'execute block') or Delphi
is capable of passing the key field back to its caller.
This means you would be able to display it on the screen
when the procedure was called from Delphi ...

If you were to massage the initial set of input data into
an external or internal table, then as this procedure can
be called from an iSQL 'execute block' construct, you could
write an 'execute block' to read the data row by row,
loading it into variables & calling the procedure
to insert both tables.

A cut-down example is:
/* */
set term ^;
create trigger TRAN_B10_Ixx for TRAN
ACTIVE BEFORE INSERT
POSITION 10
as
begin
if (inserting) then
if (NEW.TranNo is null) then
NEW.TranNo = Gen_ID(genTRANNO,1);
end^
commit^
set term ;^

-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- the master table is TRAN (keyed on TRANNO), the detail
-- is TRAN_TRADE. For non-related reasons, the insert into
-- the master table was done from another procedure - it could
-- just as easily be done in the main procedure

/* */
set term ^;
recreate procedure SUB_INSERT_TRAN(
TranNo bigint,
.....etc )
returns (TNo bigint)
as
begin
insert into TRAN (
TranNo, ...etc)
values (
:TranNo, ...etc)
returning TranNo into :TNo;
end^
set term ;^
commit;

/* */
set term ^;
recreate procedure INSERT_TRAN_TRADE(
TranNo bigint,
...etc)
returns (TNo bigint)
as
begin
execute procedure SUB_INSERT_TRAN(
:TranNo, ...etc)
returning_values :TNo;

if (TranNo is null) then TranNo = TNo;

insert into TRAN_TRADE (
TranNo, ...etc)
values (
:TranNo, ...etc);
end^
set term ;^
commit;

-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- initial data load via iSQL
set term ^;
execute block
as
declare TranNo bigint;
...etc
declare ClosedPL decimal(18,4);
begin
for
select a.TranNo, a.TranDate
...etc
from LOAD_CONTRACTS a
join ... etc
where ...etc
order by a.TranNo
into
:TranNo,
... etc
:ClosedPL
do
begin
execute procedure INSERT_TRAN_TRADE(
null, /* note null value supplied for TranNo ... */
:TranDate,
...etc)
returning_values :TNo;
end
end^
set term ;^
commit;
-->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

HTH,
John

> -------Original Message-------
> From: gregatacd@...
> Subject: [firebird-support] SQL help for dataset initialization
> Sent: 10 Feb '07 08:40
>
> Environment: Firebird 2.0
>
> I have 2 tables in a master/child relationship as follows:
>
> CREATE TABLE MASTER (
> MASTERID BIGINT NOT NULL,
> DESC VARCHAR(200)
> );
>
> CREATE TABLE CHILD (
> CHILDID BIGINT NOT NULL,
> MASTERID BIGINT NOT NULL,
> DESC VARCHAR(200)
> );
>
> MASTERID is the PK in MASTER, and CHILDID is the PK in CHILD, and
> CHILD.MASTERID is properly set up as a foreign key to MASTER.
>
> Both PKs are auto-populated with generators via an insert trigger.
>
> Now, when I create my database, I want to automatically populate these
> tables with data right from the start by executing as SQL script.
>
> For example:
>
> INSERT INTO MASTER (DESC) VALUES ("FIRST");
> INSERT INTO MASTER (DESC) VALUES ("SECOND");
> INSERT INTO MASTER (DESC) VALUES ("THIRD");
>
> This works fine for the master table, since the trigger takes care of
> the PK assignment. The problem comes when I want to insert the CHILD
> entries. I don't know what the ID is for the MASTER entry...
>
> INSERT INTO MASTER (DESC) VALUES ("FIRST");
> INSERT INTO CHILD (MASTERID, DESC)
> VALUES (<dont know this!>, 'FIRST CHILD OF FIRST');
> INSERT INTO CHILD (MASTERID, DESC)
> VALUES (<dont know this!>, 'SECOND CHILD OF FIRST');
>
> I know that in FB 2.0, there is an extended syntax
>
> INSERT INTO MASTER (DESC) VALUES ("FIRST") RETURNING MASTERID INTO
> :varMASTERID;
> INSERT INTO CHILD (MASTERID, DESC)
> VALUES (:varMASTERID, 'FIRST CHILD OF FIRST');
> INSERT INTO CHILD (MASTERID, DESC)
> VALUES (:varMASTERID, 'SECOND CHILD OF FIRST');
>
> But this requires a variable definition, which is not available in a SQL
> script such as this.
>
> I suppose I could use the new EXECUTE BLOCK statement for this, but is
> there a limitation as to the size of an EXECUTE BLOCK statement? If I
> have a relatively large dataset to initialize my database with that has
> a significant number of relationship interdependencies that require
> variable scope to be quite large, then this may not scale particularly
> well.
>
> Another option is to write an application (C++ and IBPP, for example)
>
> that does this work for me, but then anyone who is responsible for
> dealing with the default data set would have to deal with C++ instead
> of
> just living in the SQL world...
>
> What is the 'best practice' for this scenario?
>
> Thx!
>
> Greg
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> [LINK: http://mail.yahoo.com] http://mail.yahoo.com
>