CODEWITHSUNDEEP
sqldatabasedb2common-table-expression
J.Azur
J.Azur

Reputation: 43

DB2 Using with statement to keep last id

In my project I need to create a script that insert data with auto generate value for the primary key and then to reuse this number for foreign on other tables.

I'm trying to use the WITH statement in order to keep that value.

For instance, I'm trying to do this:

WITH tmp as (SELECT ID FROM (INSERT INTO A ... VALUES ...))
INSERT INTO B ... VALUES tmp.ID ...

But I can't make it work. Is it at least possible to do it or am I completely wrong???

Thank you

Upvotes: 0

Views: 585

Answers (2)

mao
mao

Reputation: 12287

Yes, it is possible, if your DB2-server version supports the syntax.

For example:

create table xemp(id bigint generated always as identity, other_stuff varchar(20));

create table othertab(xemp_id bigint);

SELECT id FROM FINAL TABLE
    (INSERT INTO xemp(other_stuff)
     values ('a'), ('b'), ('c'), ('d')
    ) ;

The above snippet of code gives the result below:

ID                  
--------------------
                   1
                   2
                   3
                   4

  4 record(s) selected.

If you want to re-use the ID to populate another table:

with tmp1(id) as ( SELECT id FROM new TABLE (INSERT INTO xemp(other_stuff) values ('a1'), ('b1'), ('c1'), ('d1') ) tmp3 )
, tmp2 as (select * from new table (insert into othertab(xemp_id) select id from tmp1 ) tmp4 )
select * from othertab;

Upvotes: 1

nitin.sharma0180
nitin.sharma0180

Reputation: 481

As per my understanding

You will have to create an auto-increment field with the sequence object (this object generates a number sequence).

You can CREATE SEQUENCE to achieve the auto increment value :

CREATE SEQUENCE seq_person
  MINVALUE 1
  START WITH 1
  INCREMENT BY 1
  CACHE 10

Upvotes: 0

Related Questions