3. So we need to check if:
- Is there data in column xy the "source"-table? -> Use this source / value
- Is there a default-value in column xy for this specific customer provided? -> Use this source / value
- No initial source or no customer-based default declared? -> Use general default
This would be the COALESCE() command.
Also, your 'customer_defaults' table needs to be in a WIDE format.
create table customer_defaults (
customer_id int primary key
This way you can:
insert into PO_LINEITEMS ( column1 -- etc )
select coalesce( ST.column1, CD.table1_column1, MyConstants.table1_column1_default ) as COLUMN1
from staging_table ST
join PO P on (/* what ever makes a PO unique within the staging table
New POs were bulk-created in a prior step
left outer join customer_defaults CD on (ST.customer_id=CD.customer_id);