incrementing numbers for insert statement

techday · #1 (**permalink**) 11-29-11, 01:05

DB2 Z/OS VERSION 10.1

create table mytable (
sno int not null,
desc varchar(15)
)
sno has unique index.

I have a insert statement that gets its values from a select statement

insert into mytable
(desc)
select column2 from table2;

Is there a way I can populate the sno value with sequential numbers without using identity/sequence number/rownumber?
Essentially something like i+1?

I tried the following, (table2 has 10 rows)
but all the rows selected have the same value (2) and index is violated...
declate i int;
set i = 1;
insert into mytable
(sno,desc)
select i+1,column2 from table2;

If its possible what would the correct syntax be?

Stealth_DBA · #2 (**permalink**) 11-29-11, 05:57

techday, I am not in a place to test this out but this should work:

Code:

INSERT INTO MYTABLE
WITH MAX_TAB(SNO_MAX)
  AS (
      SELECT MAX(SNO)
      FROM MYTABLE
     )
SELECT ROW_NUMBER() OVER(ORDER BY COLUMN2) + SNO_MAX, COLUMN2
FROM MAX_TAB
   , TABLE2

However, if more than one person is running the process at one time, there could be situations where SNO value is duplicated.

Marcus_A · #3 (**permalink**) 11-29-11, 14:46

This should work for DB2 LUW (but not sure about DB2 z/OS) :

Code:

CREATE TABLE "DBA     "."VENDOR"  (
                  "VENDOR_ID" INTEGER NOT NULL ,
                  "VENDOR_NM" VARCHAR(25) )
                 IN "USERSPACE1" ;

ALTER TABLE "DBA     "."VENDOR"
        ADD CONSTRAINT "PK_VENDOR" PRIMARY KEY
                ("VENDOR_ID");

CREATE TABLE "DBA     "."NEXT_AVAIL_ID"  (
                  "APPLICATION_ID" SMALLINT NOT NULL ,
                  "NEXT_ID" INTEGER NOT NULL )
                 IN "USERSPACE1" ;

ALTER TABLE "DBA     "."NEXT_AVAIL_ID"
        ADD CONSTRAINT "PK_NEXT_AVAIL_ID" PRIMARY KEY
                ("APPLICATION_ID");


create procedure dba.test ()
begin
declare v_next_id int;
set v_next_id = (select next_id from final table (update dba.next_avail_id set next_id = next_id + 1 where application_id = 1));
insert into dba.vendor values (v_next_id, 'IBM');
commit;
end@

techday · #4 (**permalink**) 12-05-11, 04:01

Quote:

Originally Posted by Stealth_DBA

techday, I am not in a place to test this out but this should work:

Code:

INSERT INTO MYTABLE
WITH MAX_TAB(SNO_MAX)
  AS (
      SELECT MAX(SNO)
      FROM MYTABLE
     )
SELECT ROW_NUMBER() OVER(ORDER BY COLUMN2) + SNO_MAX, COLUMN2
FROM MAX_TAB
   , TABLE2

However, if more than one person is running the process at one time, there could be situations where SNO value is duplicated.

Stealth, that was a nice trick to add to row number.
But I do not want to use row number...
Also, small modifcation to do IFNULL in case mytable has no records..

Code:

INSERT INTO MYTABLE
WITH MAX_TAB(SNO_MAX)
  AS (
      SELECT IFNULL(MAX(SNO),0)
      FROM MYTABLE
     )
SELECT ROW_NUMBER() OVER(ORDER BY COLUMN2) + SNO_MAX, COLUMN2
FROM MAX_TAB
   , TABLE2

techday · #5 (**permalink**) 12-05-11, 04:04

Quote:

Originally Posted by Marcus_A

This should work for DB2 LUW (but not sure about DB2 z/OS) :

Code:

create procedure dba.test ()
begin
declare v_next_id int;
set v_next_id = (select next_id from final table (update dba.next_avail_id set next_id = next_id + 1 where application_id = 1));
insert into dba.vendor values (v_next_id, 'IBM');
commit;

Marcus, not sure how this works..
First we need to have a row inserted in dba.next_avail_id with application_id 1.?
Even after this, I think only one row is going to have the next seq number.
But i need to assign seq num for mutiple rows with source as another select.

Marcus_A · #6 (**permalink**) 12-05-11, 04:08

Quote:

Originally Posted by techday

Marcus, not sure how this works..
First we need to have a row inserted in dba.next_avail_id with application_id 1.?
Even after this, I think only one row is going to have the next seq number.
But i need to assign seq num for mutiple rows with source as another select.

My example allows for multiple next available numbers for multiple tables. But inserts for a given table would only use one of the next available number rows designated by the application_id.

techday · #7 (**permalink**) 12-05-11, 04:24

ok thanks.

Internet Services

Web Development

Digital Marketing

Consumer Tech