CODEWITHSUNDEEP
sqldb2
Mo.
Mo.

Reputation: 42493

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

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

Upvotes: 6

Views: 48160

Answers (4)

Peter Miehle
Peter Miehle

Reputation: 6070

Maybe you should specify the columns as:

col2 smallint not null 
     generated by default as identity (start with 1, increment by 1)

and insert into table1 select col1, default from table2

Upvotes: 1

Deepa
Deepa

Reputation: 411

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

  1. GENERATE_UNIQUE function
  2. IDENTITY column
  3. SEQUENCE object

Assuming col2_SEQ is created similar to below statement:

    CREATE SEQUENCE col2_SEQ  
    AS INTEGER  
    START WITH 1  
    INCREMENT BY 1  
    NO MINVALUE  
    NO MAXVALUE  
    NO CYCLE  
    ORDER

The insert statement can be written as follows:

    INSERT INTO Table1 (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

Upvotes: 1

kns
kns

Reputation: 31

There is also alternative syntax now, which worked for me in DB2 10.x 

INSERT INTO table1 (col1, col2)
SELECT col1, schema.seq_name.nextval
FROM table2;

Upvotes: 0

bhamby
bhamby

Reputation: 15450

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

Upvotes: 5

Related Questions