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 );