Multiple INSERT

[grigno]

Hi,

I have to execute an insert like this:

INSERT INTO TAB1 (F1, F2)
SELECT (?), F2_T FROM TAB2
The field F1 of TAB1 is the primary key of my table and is not
auto-increment.
In TAB2 I have more than one record.
In stad of (?) i'd like to put an expression like: SELECT MAX(F1)+1
FROM TAB1

so:

INSERT INTO TAB1 (F1, F2)
SELECT (SELECT MAX(F1)+1 FROM TAB1), F2_T FROM TAB2

Unfortunatelly this don't wotks because the value of (SELECT MAX(F1)+1
FROM TAB1) is always the same, so i have an error of duplicate PK.

Onyone has an idea ?

thanks to all...

[Serge Rielau]

grigno wrote:

Hi,

I have to execute an insert like this:

INSERT INTO TAB1 (F1, F2)
SELECT (?), F2_T FROM TAB2
The field F1 of TAB1 is the primary key of my table and is not
auto-increment.
In TAB2 I have more than one record.
In stad of (?) i'd like to put an expression like: SELECT MAX(F1)+1
FROM TAB1

so:

INSERT INTO TAB1 (F1, F2)
SELECT (SELECT MAX(F1)+1 FROM TAB1), F2_T FROM TAB2

Unfortunatelly this don't wotks because the value of (SELECT MAX(F1)+1
FROM TAB1) is always the same, so i have an error of duplicate PK.

Onyone has an idea ?

thanks to all...

CREATE SEQUENCE s;
INSERT INTO TAB1 (F1, F2)
SELECT (SELECT MAX(F1) FROM TAB1) + NEXT VALUE FOR s, F2_T
FROM TAB2;

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Knut Stolze]

grigno wrote:

Hi,

I have to execute an insert like this:

INSERT INTO TAB1 (F1, F2)
SELECT (?), F2_T FROM TAB2
The field F1 of TAB1 is the primary key of my table and is not
auto-increment.
In TAB2 I have more than one record.
In stad of (?) i'd like to put an expression like: SELECT MAX(F1)+1
FROM TAB1

so:

INSERT INTO TAB1 (F1, F2)
SELECT (SELECT MAX(F1)+1 FROM TAB1), F2_T FROM TAB2

Unfortunatelly this don't wotks because the value of (SELECT MAX(F1)+1
FROM TAB1) is always the same, so i have an error of duplicate PK.

Untested:

INSERT INTO tab1(f1, f2)
SELECT ( SELECT MAX(f1)
FROM tab1 ) + rn,
f2_t
FROM ( SELECT row_number() over(), f2_t
FROM tab2 ) AS t(rn, f2_t)

--
Knut Stolze
Information Integration
IBM Germany / University of Jena