Use a returning value from an INSERT table (A) into another table (B)

Question

I need to use the returning value of the Table A (ID) and insert it in Table B as a parameter:

insert into tableA (ID, Name , Address) 
values (GEN_ID(GENERATOR,1),'John','123 street') 
returning ID
--Example: ID=159
    
insert into tableB (ID, TABLE_A_FK ) 
  values (GEN_ID(GENERATOR,1), 159) 

Instead of entering the actual value 159, can I create like a variable (e.g. declare ID int;), and just pass the parameter?

Answer 1

The only way to do this in a single statement, is to use EXECUTE BLOCK (basically an anonymous one-off procedure). It uses the same syntax as normal stored procedures in Firebird.

You can do:

execute block
as
  declare id integer;
begin
  insert into tableA (ID, Name , Address) 
    values (GEN_ID(GENERATOR,1), 'John', '123 street') 
    returning ID
    into id;

  insert into tableB (ID, TABLE_A_FK) 
    values (GEN_ID(GENERATOR,1), :id);
end

If necessary, execute block statements can be parameterized, so you can use parameters to provide values (instead of hard coding them). See the link above for details.