0

We have a scenario where we need to add multiple rows to different tables, get the generated primary key ID's, then create more inserts with FK's referring those previously generated ID's, etc. On SQL Server you could just use the OUTPUT clause to store the inserted rows and the generated ID's and then refer to them. But apparently on db2 you can't use FINAL TABLE results for anything besides a select.

So we're looking to insert a bunch of data from an excel sheet into table A, then match the generated ID's into the excel dataset, and insert more rows on table B, C, etc.

Is there an alternate way of storing the inserted records in a usable format. Preferrably without something that demands compiling a procedure, so we can just run execute the script one part at a time? A quasi-code example below, which obviously doesn't work.

DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP
    (ITEM_IDENTIFIER,
    TABLE1_ID INT) 
ON COMMIT PRESERVE ROWS;

INSERT INTO SESSION.TEMP (ITEM_IDENTIFIER, TABLE1_ID)
SELECT IDENTIFIER, ID
FROM FINAL TABLE 
    (INSERT INTO MYSCHEMA.MYTABLE (IDENTIFIER, ID /*...other columns*/)
    SELECT *
    FROM 
        (SELECT 'asd123' IDENTIFIER /*...other columns*/ FROM SYSIBM.SYSDUMMY1
        UNION
        SELECT 'bsb234' /*...other columns*/ FROM SYSIBM.SYSDUMMY1
        UNION
        SELECT 'dasd654_' /*...other columns*/ FROM SYSIBM.SYSDUMMY1
        /* etc.. */) s
    );

1 Answer 1

2

You can do this by making the outer statement a select as well:

WITH T (ITEM_IDENTIFIER, TABLE1_ID) AS (
  SELECT IDENTIFIER, ID
  FROM FINAL TABLE 
    (INSERT INTO MYSCHEMA.MYTABLE (IDENTIFIER, ID /*...other columns*/)
    SELECT *
    FROM 
        (SELECT 'asd123' IDENTIFIER /*...other columns*/ FROM SYSIBM.SYSDUMMY1
        UNION
        SELECT 'bsb234' /*...other columns*/ FROM SYSIBM.SYSDUMMY1
        UNION
        SELECT 'dasd654_' /*...other columns*/ FROM SYSIBM.SYSDUMMY1
        /* etc.. */) s
    )
)
SELECT * FROM FINAL TABLE (
  INSERT INTO SESSION.TEMP (ITEM_IDENTIFIER, TABLE1_ID)
  SELECT ITEM_IDENTIFIER, TABLE1_ID FROM T
)

You can chain multiple DML statements in this way by wrapping all but the last one in a CTE.

Fiddle

4
  • Just a note for the OP, I often find it easiest to use cte's for all the work, and then use a select count(1) from ... at the outermost level. A typical example is deleting from one table and inserting into another: with del (...) , ins (....) select count(1) from ins
    – Lennart
    Sep 30, 2021 at 14:05
  • You missed a reference to the final CTE table in the final insert. Otherwise perfect, this works!
    – Kahn
    Oct 1, 2021 at 9:56
  • Is it possible to do an update with this same idea btw? Basically following use cases would insert new data matching by the ID's generated here, and then update the TEMP table to show which ID is connected to which. But db2 doesn't allow UPDATE JOIN statements, only MERGE. So is there another way of doing it?
    – Kahn
    Oct 1, 2021 at 10:18
  • You don't need a join to do an update; you need a correlated subselect.
    – mustaccio
    Oct 1, 2021 at 11:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.