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]

Re: Multiple INSERT

grigno wrote:[color=blue]
> 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...[/color]
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]

Re: Multiple INSERT

grigno wrote:
[color=blue]
> 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.[/color]

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