Incrementing a Column by One for Each Insert Using DB2’s Insert Into with Select

I am having trouble inserting the output of a SELECT statement that incorporates a WITH clause into a table due to syntax errors. Additionally, there are two applications in use: Application1 generates the insert statement we use in production, while Application2 runs a select query on Table2. Whenever the insert query runs, the second query experiences significant slowdowns, occasionally resulting in a read timeout.

Question:

I am attempting to transfer the data from a single column in one table to another. Additionally, I aim to assign a unique and sequentially increasing numerical value to the primary key column for each new 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

Is there an alternative method to accomplish my objective since utilizing the sequence value seems unfeasible? Essentially, I require a unique BIGINT value for each new entry in col1 of table2 to be assigned to col2 in table1.



Solution 1:

For Linux, Unix, and Windows operating systems, it appears that using

NEXT VALUE FOR sequence

alone should suffice without including the redundant select from sysdummy.

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


Solution 2:


DB2 offers three distinct techniques to create
unique values
.


  1. GENERATE_UNIQUE

    function

  2. IDENTITY

    column

  3. SEQUENCE

    object

If we consider that the

col2_SEQ

code is formulated in a manner akin to the statement presented below:

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

Here is an alternative way to express
insert statement
:

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

Additional details regarding the trio of techniques outlined earlier are accessible on this page.


Solution 3:


Additionally, there is now an alternative syntax that proved successful for me in DB2 10.x.

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


Solution 4:

Perhaps it would be beneficial to define the columns as follows:

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

and

insert into table1 select col1, default from table2

Frequently Asked Questions

Posted in Sql