CODEWITHSUNDEEP
sqldatabaseinsertdb2increment
JulioBordeaux
JulioBordeaux

Reputation: 504

db2 set incrementing values using insert statement

I have two tables:

MYTABLE1
ID   |Col1|Col2      |
--------------------- ....
64  |  50 |   7000   |



MYTABLE2
MY_ID |
-------
  64  | 
  87  |

and i need to insert like 10,000 rows to MYTABLE1 where ID=MY_ID Col1=50 ant Col2 increments each time the row is inserted so MYTABLE1 would look like that:

ID   |Col1|Col2      |
--------------------- ....
64  |  50 |   7000   |
87  |  50 |   7001   |

I have found similar solution here: insert thousands of rows in DB2

but i still cant figure out how to load all the IDs from MYTABLE2

Please help, Thank You for Your answers.

Upvotes: 0

Views: 1098

Answers (1)

Multisync
Multisync

Reputation: 8797

insert into MYTABLE1 (id, col1, col2)
select my_id, 50 col1, col2 + row_number() over(order by my_id) from
(
    select MY_ID, m.col2
    from MYTABLE2 t2, (select max(col2) as col2 from MYTABLE1) m
) t2
-- if you don't want to insert IDs which already exist in MYTABLE1
where not exists (select 1 from MYTABLE1 t1 where t1.ID = t2.MY_ID);

Upvotes: 1

Related Questions