multiple Inserts into same table from different sources

UDA
Enthusiast

multiple Inserts into same table from different sources

Please Help me.

I have one table eg. Cust(cols are a, b,c,d,e) which is sourced from table Cust_add(cols used in cust are a, c) and Cust_info(cols used in cust are b,d,e)

can i do the following inserts

Insert into cust
(a
,c)
select
a
,c
from Cust_add
;Insert into cust
(b
,d
,e)
select
b
,d
,e
from Cust_info
;

3 REPLIES
Enthusiast

Re: multiple Inserts into same table from different sources

What are the key columns in the Cust table? What are the joining keys in the source tables... ETL developer ask these questions all the time...
Enthusiast

Re: multiple Inserts into same table from different sources

Joining key is 'b' in the tables.
Also b is getting inserted from both sources(Cust_add and Cust_info) into Cust

And what if I have one table eg. Cust(cols are a, b,c,d,e) which is sourced from table Cust_add(cols used in cust are a, b,c) and Cust_info(cols used in cust are b,d,e).

Here 'b' will be sourced from both tables...then will this work or give an error? What will be the result?
Consider b as Primary Index
can i do the following inserts

Insert into cust
(a
,b
,c)
select
a
,b
,c
from Cust_add
;Insert into cust
(b
,d
,e)
select
b
,d
,e
from Cust_info
;
Enthusiast

Re: multiple Inserts into same table from different sources

If your CUST table allows duplicate row ( no UPI or USI is created to enforce data integrity and the table was created as Multiset table) then your query will work. But as someone has already pointed out, whether that inserts make any sense?

Normally in such situation you will need a OUTER JOIN ( Left, Right or FULL) between the source tables depending on data.