DB2: Insert in with select, increasing the column for each new row, one for each insert?

Question

I am trying to copy content from a column in one table to another and at the same time I want to populate the primary key column with an incremental number for each row created:

I tried to do 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 like I can't use the sequence value this way, is there any other way to achieve what I'm trying to do? I just need col2 in table1 to populate a unique BIGINT for each new record from col1 from table2

Answer 1

If you are running on Linux / Unix / Windows (and probably for others), I think you just need the NEXT VALUE FOR sequence . You do not need additional choice from sysdummy in this context.

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

Answer 2

There are 3 methods in which unique values ​​can be generated in DB2.

• Function GENERATE_UNIQUE
• IDENTITY column
• SEQUENCE object

Assuming col2_SEQ is created as described below: CREATE SEQUENCE col2_SEQ
AS INTEGER
STARTING FROM 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
NO CYCLE
ORDER

The insert statement can be written as follows: INSERT INTO Table 1 (col1, col2) VALUES ((SELECT col1 FROM table2), NEXT VALUE for col2_SEQ)

More information on each of the three methods mentioned above can be found here http://www.ibm.com/developerworks/data/library/techarticle/0205pilaka/0205pilaka2.html

Answer 3

perhaps you will describe the columns as: col2 smallint not null, generated by default as an identifier (starting at 1, incrementing by 1)

and insert into table1 select col1, by default from table2