multi-table insert in Oracle 9i

[Niloufar]

Could someone please tell me what am I doing wrong that causes both of
the following multi-table insert statements to fail with a "missing key"
error. My Oracle server is Version 9.2.0.2.0.

Thanks,
Niloufar

************************************************** *****************************
create table junk(id number,order_num varchar2(10),bill_amt number);
create table junk2(id2 number,order_num2 varchar2(10),bill_amt2 number);
create table junk_input(order_id number,order_number
varchar2(10),bill_amt number);

insert into junk_input values (1, '1234', 10);
insert into junk_input values (2, '1234', 10);
insert into junk_input values (3, '1234', 10);
insert into junk_input values (4, '5678', 10);
insert into junk_input values (5, '5678', 10);
select * from junk_input;

tried both:

insert all when 1=1
into junk (id, order_num, sequence_num)
select order_id, order_number, seq_num from junk_input;

and tried:

insert all when 1=1
into junk (id, order_num, sequence_num)
when order_number = '5678'
into junk2 (id2, order_num2, sequence_num2)
select order_id, order_number, seq_num from junk_input;

[Dave Hau]

You're missing a "THEN" keyword:

insert all when 1=1 THEN
into junk (id, order_num, sequence_num) ...

Also, if the condition is 1=1, you don't need to specify it at all:

insert all
into junk (id, order_num, sequence_num) ...

does the same thing.

Cheers,
Dave




Niloufar wrote:

> Could someone please tell me what am I doing wrong that causes both of
> the following multi-table insert statements to fail with a "missing key"
> error. My Oracle server is Version 9.2.0.2.0.
>
> Thanks,
> Niloufar
>
> ************************************************** *****************************
>
> create table junk(id number,order_num varchar2(10),bill_amt number);
> create table junk2(id2 number,order_num2 varchar2(10),bill_amt2 number);
> create table junk_input(order_id number,order_number
> varchar2(10),bill_amt number);
>
> insert into junk_input values (1, '1234', 10);
> insert into junk_input values (2, '1234', 10);
> insert into junk_input values (3, '1234', 10);
> insert into junk_input values (4, '5678', 10);
> insert into junk_input values (5, '5678', 10);
> select * from junk_input;
>
> tried both:
>
> insert all when 1=1
> into junk (id, order_num, sequence_num)
> select order_id, order_number, seq_num from junk_input;
>
> and tried:
>
> insert all when 1=1
> into junk (id, order_num, sequence_num)
> when order_number = '5678'
> into junk2 (id2, order_num2, sequence_num2)
> select order_id, order_number, seq_num from junk_input;
>
>

[Niloufar]

Thank you Dave.

I feel silly that I didn't pay more attention to the syntax.

However, regarding the "when 1=1" clause, if I have multiple "when"
clauses in the statement, I can not execute it if the first "inert into"
doesn't have a when clause. I get a "missing select keyword" error.

Thanks,
Niloufar

Dave Hau wrote:

> You're missing a "THEN" keyword:
>
> insert all when 1=1 THEN
> into junk (id, order_num, sequence_num) ...
>
> Also, if the condition is 1=1, you don't need to specify it at all:
>
> insert all
> into junk (id, order_num, sequence_num) ...
>
> does the same thing.
>
> Cheers,
> Dave
>
>
>
>
> Niloufar wrote:
>

>> Could someone please tell me what am I doing wrong that causes both of
>> the following multi-table insert statements to fail with a "missing
>> key" error. My Oracle server is Version 9.2.0.2.0.
>>
>> Thanks,
>> Niloufar
>>
>> ************************************************** *****************************
>>
>> create table junk(id number,order_num varchar2(10),bill_amt number);
>> create table junk2(id2 number,order_num2 varchar2(10),bill_amt2 number);
>> create table junk_input(order_id number,order_number
>> varchar2(10),bill_amt number);
>>
>> insert into junk_input values (1, '1234', 10);
>> insert into junk_input values (2, '1234', 10);
>> insert into junk_input values (3, '1234', 10);
>> insert into junk_input values (4, '5678', 10);
>> insert into junk_input values (5, '5678', 10);
>> select * from junk_input;
>>
>> tried both:
>>
>> insert all when 1=1
>> into junk (id, order_num, sequence_num)
>> select order_id, order_number, seq_num from junk_input;
>>
>> and tried:
>>
>> insert all when 1=1
>> into junk (id, order_num, sequence_num)
>> when order_number = '5678'
>> into junk2 (id2, order_num2, sequence_num2)
>> select order_id, order_number, seq_num from junk_input;
>>
>>

>

[shonali]

right answer is this
for single table
SQL> insert all when 1=1 then
2 into junk (id, order_num,bill_amt)
3 select order_id, order_number,bill_amt from junk_input;


for multitable
SQL> insert all when 1=1 then
2 into junk (id, order_num,bill_amt)
3 when order_number = '5678' then
4 into junk2 (id2, order_num2,bill_amt2)
5 select order_id, order_number,bill_amt from junk_input;