[SOLVED] firebird insert into returning into another insert

firebird insert into returning into another insert

I'm using Firebird as DB and I need to do this:

INSERT INTO TG (ID, UID, GID) 
    SELECT (INSERT INTO TBO VALUES (GEN_ID('o',1)) RETURNING ID), UID, 10 
    FROM TBL l 
    WHERE l.is=1

the part with select is OK when I use:

SELECT (GEN_ID('o',1)), UID, 10 
    FROM TBL l 
    WHERE l.is=1

but I need the ID in other table for dependency first.

I know about something called procedures but I have no idea how to use them. Is there an option to do this using SQL?

Solution

Take a look at the EXECUTE BLOCK statement. It allows you to execute multiple statements in one "batch" or write complex logic if you cannot embed it in one SQL query.

Inside EXECUTE BLOCK you can write multiple commands using PSQL.

EB allows input parameters, output parameters (yes you can use it as a table), local variables, if statement, while, for select etc. A very powerful tool.

Just prepare your block and execute it like simple SQL query.