Is like that, I have A table. A, now there is A select collection of B, and C, (because the insert target table is 2, so can't use the merge into ps: or which Daniel has A good way,,,) I need to use the table data, A data collection with B according to field A, B do judgment (where A.a=B.a and a.=B.b), if the table has A the insert to the record table C, if there is no this record, insert into the table. A, how to write for Daniel insert all, just contact oracle 2 days, I hope you can teach me,

CodePudding user response:

Stored procedure write
Idea is, defines a cursor to receive your collection
Loop cursor, access to object and judge which is inserted into the table

CodePudding user response:

First insert A table: merge into A using B on (A.a=B.a and a.=B.b) when not matched insert A...
Then insert table C: insert into C select * from A, B where A.a=B.a and a.=B.b

CodePudding user response:

refer to the second floor mayanzs response:

first insert A table: merge into A using B on (A.a=B.a and a.=B.b) when not matched THEN insert A...
And in table C insert: insert into C select * from where the EXISTS (select * from A where clause A.a=B.a and a.=B.b)

Similar to the second floor, fine-tuning,,,

CodePudding user response:

To insert table C
Insert into table C
Select table field b
From table B
Where the exists (select A table. The fields A
From A table
Where A table. A=B table. And A table. A table B=B. B)
Insert A table
Insert into A table
Select table field b
From table B
Where not the exists (select A table. The fields A
From A table
Where A table. A=B table. And A table. A table B=B. B)

CodePudding user response:

My ways to solve your problem, has been tested,
List as follows:
The create table a (a varchar2 (10), b varchar2 (10), c varchar2 (10));
The create table b (a varchar2 (10), b varchar2 (10), c varchar2 (10));
The create table c (a varchar2 (10), b varchar2 (10), c varchar2 (10));
Insert into a (a, b, c) values (1, 2, 1);
Insert into a (a, b, c) values (1,3,1);
Insert into b (a, b, c) values (1,2,2);
Insert into b (a, b, c) values (1,4,2);
commit;
Select * from a;
Select * from b;
Select * from c;
Insert all
The when c4 is not null then
Into c (a, b, c) values (c1, c2 and c3)
The else
Into a (a, b, c) values (c1, c2 and c3)
The select b.a as c1, b.b as c2, biggest as c3, a.a as c4
The from B left join A on A.a=B.a and a.=B.b;

Select * from a;
Select * from c;

CodePudding user response:

Just also tried the merge statement, it can't handle two insert statement