Is it possible to write a "Insert all into <table_1> ( col_1,col_2,col_3 ) values ( col_a,col_b,col_c)
into <table_2> ( col_1,col_2,col_3 ) values ( col_a,col_b,col_c)
into <table_3> ( col_1,col_2,col_3 ) values ( col_a,col_b,col_c)
select col_a, col_b, col_c
from source_table"
( the above sql works well but the twist i have is )
with an EXISTS clause that differs in terms of values for each of the 3 Tables.
Let's say : select col_a, col_b, col_c from source_table - has 10 rows.
< Table_1 > already has 4 of these rows, so only 6 rows need to be inserted, to not cause duplication
< Table_2 > already has 6 of these rows, so only 4 rows need to be inserted, to not cause duplication
< Table_3 > already has 8 of these rows, so only 2 rows need to be inserted, to not cause duplication.
since the "EXISTS Rows" would vary by <Table_1>, <Table_2> and <Table_3> - the 3 destination tables,
is it possible to achieve this with an "INSERT ALL" ?
If Yes, please help.
Thanks in advance for your time.
SQL>
SQL> create table t1 as select rownum x from dual connect by level <= 5;
Table created.
SQL> create table t2 as select rownum y from dual connect by level <= 3;
Table created.
SQL> create table t3 as select rownum z from dual connect by level <= 6;
Table created.
SQL> create table t_source as select rownum s from dual connect by level <= 10;
Table created.
SQL>
SQL> select * from t1 order by 1;
X
----------
1
2
3
4
5
5 rows selected.
SQL> select * from t2 order by 1;
Y
----------
1
2
3
3 rows selected.
SQL> select * from t3 order by 1;
Z
----------
1
2
3
4
5
6
6 rows selected.
SQL>
SQL> insert all
2 when in_tab1 is null then
3 into t1 (x ) values (s )
4 when in_tab2 is null then
5 into t2 (y ) values (s )
6 when in_tab3 is null then
7 into t3 (z ) values (s )
8 select
9 t_source.s,
10 t1.x in_tab1,
11 t2.y in_tab2,
12 t3.z in_tab3
13 from t_source, t1, t2, t3
14 where t_source.s = t1.x(+)
15 and t_source.s = t2.y(+)
16 and t_source.s = t3.z(+)
17 /
16 rows created.
SQL>
SQL> select * from t1 order by 1;
X
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
SQL> select * from t2 order by 1;
Y
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
SQL> select * from t3 order by 1;
Z
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
SQL>
SQL>