Use inserted value as a parameter for other inserts

Question

There is a db2 database with two tables. The first one, table1, has autoincrement column ID. It is the foreign key for the table2.

A am writing an HTML generator for SQL queries. So with some input parameters it generates a query or multiple queries. It is not connected to the database. What I need is to get that autoincrement field and use it in next queries. So basically, the scenario is:

1. insert into table1;
2. select autogenerated field ID;
3. insert into table2 using that ID;
4. insert into table2 using that ID;
5. ...some more similar inserts...
6. insert into table2 using that ID;

And all that SQL query should be generated and then used as a single SQL script.

I was thinking about something like this:

SELECT ID FROM FINAL TABLE (INSERT INTO Table1 (t1column1, t1column2, etc.) VALUES (t1value1, t1value2, etc.))

But I don't know, how I can write the result into a variable so I could use it in next queries like this:

INSERT INTO Table2 (foreignKeyCol, t2column1, t2column2, etc.) VALUES ($ID, t2value1, t2value2, etc.)

I could just paste that select instead of $ID, but the second query can be used several times with the same $ID and different values.

EDIT: DB2 10.5 on Linux.

Answer 1

You can chain several inserts together using CTEs, like so:

WITH idcte (id) as ( 
 SELECT ID FROM FINAL TABLE (
   INSERT INTO Table1 (t1column1, t1column2, etc.)
   VALUES (t1value1, t1value2, etc.)
 )
),
ins1 (id) as (
  SELECT foreignKeyCol FROM FINAL TABLE (
    INSERT INTO Table2 (foreignKeyCol, t2column1, t2column2, etc.)
    SELECT id, t2value1, t2value2, etc.
    FROM idcte
  )
),
-- more CTEs
SELECT foreignKeyCol FROM FINAL TABLE (
    -- your last INSERT ... SELECT FROM
) 

Essentially you will have to wrap each INSERT into a SELECT FROM FINAL TABLE for this to work.

Alternatively, you can use a global variable to keep the ID value:

CREATE VARIABLE myNewId INT;
SET myNewId = (SELECT ID FROM FINAL TABLE (
   INSERT INTO Table1 (t1column1, t1column2, etc.)
   VALUES (t1value1, t1value2, etc.)
));
INSERT INTO Table2 (foreignKeyCol, t2column1, t2column2, etc.)
            VALUES (myNewId, t2value1, t2value2, etc.);
DROP VARIABLE myNewId;

This assumes a recent version of Db2 for LUW.