How to Add Multiple Rows to a DB2 Table with Generated Always Identity Column

db2db2-luw

This more of a Q/A question, perhaps someone is facing the same problem, so I'll ask a question and then answer it.

Say that we have a table defined as:

CREATE TABLE T (x BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY);

and need to generate one row for each row in another table. For one row we could use:

INSERT INTO T (x) VALUES (DEFAULT);

However:

INSERT INTO T (x) SELECT DEFAULT FROM SRCTABLE

does not work, and generates the following error:

SQL0206N  "DEFAULT" is not valid in the context where it is used.
SQLSTATE=42703

How do I add multiple rows to such table without resorting to a cursor?

Best Answer

One way is to use MERGE (best thing since sliced bread?). 

MERGE INTO T x
USING (
    SELECT 1 FROM SRCTABLE
) y (dummy)
    ON 1=0 -- Always false
WHEN NOT MATCHED THEN
    INSERT (x) VALUES (DEFAULT);

This will add one row to T for every row in SRCTABLE.

It would be cool if we could grab the generated values with the use of transition tables, but it appears as if this does not work for MERGE (db2 10.5, have not checked 11 yet):

SELECT * FROM NEW TABLE (
    MERGE INTO T x
    USING (
        SELECT 1 FROM SRCTABLE
    ) y (dummy)
        ON 1=0 -- Always false
    WHEN NOT MATCHED THEN
        INSERT (x) VALUES (DEFAULT)
); 

SQL0104N  An unexpected token "select * from new table (" was found 
following "BEGIN-OF-STATEMENT".  Expected tokens may include:  "<space>".  
SQLSTATE=42601

Compare with:

SELECT * FROM NEW TABLE (
    INSERT INTO T (x)
    VALUES (DEFAULT)
);
Related Question