[SOLVED] dashDB: insert into generated default column using select

dashDB: insert into generated default column using select

I have a simple test table

CREATE TABLE TEST (
KEY INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
INTENTS VARCHAR(255),
NO_FOUND SMALLINT );

I am then trying to insert data into this table using the following command from within dashDB's sql dashboard.

Insert into table from (select item1,item2,item3 from TEST2 where some_condition );

However I cannot seem to get the command to not return an error. Have tried the db2 'DEFAULT', and '0' (default for integer), and even NULL as values for item1.

Have also tried the insert using values, but then the column headings cause the system to report multiple values returned.

Have also tried 'OVERRIDING USER VALUE' but this then complains about not finding a JOIN element.

Any ideas welcome.

Solution

I would try something like this:

Insert into test(intents,no_found)
(select item2,item3 from TEST2 where some_condition );

You specify that only two of the three columns receive values, the KEY column is generated. Hence you only select the two related columns.