Sql – DB2: Insert into with select, incrementing a column for each new row by one for each insert

db2sql

Im trying to copy the contents from a column in one table to another and at the same time want to populate the primary key column with an incrementing number for each row created:

I have tried doing the following:

INSERT INTO Table1 (col1, col2) VALUES((SELECT col1 FROM table2), (SELECT NEXTVAL FOR col2_SEQ FROM sysibm.sysdummy1));

but get the following error:

DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0348N  "NEXTVAL FOR col2_SEQ" cannot be specified in this 
context.  SQLSTATE=428F

It seems that i cant use the sequence value in this way, is there any other way I can achieve what I'm trying to do? I just need col2 in table1 to be populated with a unique BIGINT for each new entry from col1 from table2

Best Solution

If you're on Linux/Unix/Windows (and probably for others), I think you just want NEXT VALUE FOR sequence. You don't need the extra select from sysdummy in this context.

INSERT INTO table1 (col1, col2)
    SELECT col1, NEXT VALUE FOR col2_SEQ
    FROM table2