Hi,

you must have FB2.5 to do this from one statement
look at "execute statement" extension in FB2.5 release notes

Karol Bieniaszewski

--- In firebird-support@yahoogroups.com, "ehaerim" <ehaerim@...> wrote:
>
> There are two databases: A.fdb and B.fdb.
> You can think of A.fdb as the whole history database up to yesterday and B.fdb as today's database.
> I need to merge B.fdb into A.fdb.
> [1] Update or insert records from B.fdb's I2 into A.fdb's I2
> [2] Update or insert records from B.fdb's A2 into A.fdb's A2
> [3] Update or insert records from B.fdb's D2 into A.fdb's D2
>
> I am trying to do these using Procedure Merge as shown below.
> Not sure how to handle two databases at the same time.
> I need some help on completing this procedure.
>
> thx
> HR
>
> <Database A.fdb>
>
> create table I2
> (
> ID integer not null primary key,
> INST varchar(32) not null unique
> );
>
> create table A2
> (
> ALI varchar(32) not null unique,
> ID integer not null references I2(ID) on update cascade on delete cascade
> );
>
> create table D2
> (
> ID integer not null,
> YMD date not null,
> TB integer,
> MB integer,
> DB integer,
> primary key(ID, YMD),
> foreign key(ID) references I2(ID) on update cascade on delete cascade
> );
>
> create sequence SeqInst;
>
> set term ^ ;
> create trigger trg_I2_BI_P0 for i2
> active before insert position 0
> as
> begin
> if ((new.id is null) or (new.id = 0)) then
> begin
> new.id = gen_id(SeqInst, 1);
> end
> end
> ^
> set term ; ^
>
>
> <Database B.fdb>
>
> create table I2
> (
> ID integer not null primary key,
> INST varchar(32) not null unique
> );
>
> create table A2
> (
> ALI varchar(32) not null unique,
> ID integer not null references I2(ID) on update cascade on delete cascade
> );
>
>
> create table D2
> (
> ID integer not null,
> YMD date not null,
> TB integer,
> MB integer,
> DB integer,
> primary key(ID, YMD),
> foreign key(ID) references I2(ID) on update cascade on delete cascade
> );
>
> create sequence SeqInst;
>
> set term ^ ;
> create trigger trg_I2_BI_P0 for i2
> active before insert position 0
> as
> begin
> if ((new.id is null) or (new.id = 0)) then
> begin
> new.id = gen_id(SeqInst, 1);
> end
> end
> ^
> set term ; ^
>
>
> set term ^ ;
>
> CREATE PROCEDURE MERGE(DB1 VARCHAR(256), DB1 VARCHAR(256))
> AS
> DECLARE VARIABLE ID INTEGER;
> BEGIN
> CONNECT :DB1 AS DB_1 USER 'SYSDBA' PASSWORD 'MASTERKEY';
> CONNECT :DB2 AS DB_2 USER 'SYSDBA' PASSWORD 'MASTERKEY';
>
> /* [1] */
> ...
>
> /* [2] */
> ...
>
> /* [3] */
> ...
>
> END
> ^
>
> set term ; ^
>