DB2 increment value for each insert into

Question

I have 2 tables. I want to insert data from table2 to table1, by incrementing a value (id) for each insert from table2 into table1 (row by row).

table1

id	name
348	Mike
349	John

table2

name
Conny
Fred
Bond
Fred

For each insert it should be ensured that the next higher (max value) of table1 is queried and the insert is inserted. And only unique values from table2 should inserted, no duplicates.

Thank you

Have a look: https://stackoverflow.com/questions/13466347/how-to-auto-increment-in-db2 — PM 77-1, Dec 24 '21 at 16:39

score 0 · Answer 1 · answered Dec 25 '21 at 03:52

You can do:

insert into table1 (id, name)
select
  row_number() over(order by name) + (select max(id) from table1),
  name
from (
  select distinct name as name
  from table2 x
  where not exists (
    select name from table1 y where y.name = x.name
  )
) x;

Result:

 ID   NAME  
 ---- ----- 
 348  Mike  
 349  John  
 350  Bond  
 351  Conny 
 352  Fred

See running example at db<>fiddle.

DB2 increment value for each insert into

1 Answers1