How to run multi table insertion in a single transaction?
Moderators: chulett, rschirm, roy
How to run multi table insertion in a single transaction?
Hi
how can we insert data into 2 different tables from transformer in a single transaction?
Scenario : Think of Master table and child table. From seq file i am getting data to both Master and child in a single row, i am separating the data and would liek to insert master data first and up on success insert the child records(multiple records) in a single transcation?
Any help would be greatly appreciated.
how can we insert data into 2 different tables from transformer in a single transaction?
Scenario : Think of Master table and child table. From seq file i am getting data to both Master and child in a single row, i am separating the data and would liek to insert master data first and up on success insert the child records(multiple records) in a single transcation?
Any help would be greatly appreciated.
Hari
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Hi Zenith,
As per your suggestion, tried to insert data into Master & Detail table using
Oracle OCI stage by checking Enable Transaction Grouping property in Transaction Handling tab.
However i am facing issues while loading data into master table.
For Example
Source Data :
col1 Col2 Col2
1 aa bb
2 bb cc
Master Table : Table1
Detail Table : Table2
In Transformer, condition used for Master Table link is
trim(Source col1)<>trim(lkp col1)
condition for Detail link is
trim(Source col1)=trim(lkp_col1)
Before first run there is no data in both Master & Detail table.
However, For First time run Data is not populated in the Master table even though Link contrain is satisfied.
Can you please suggest to how to handle this issue
Thanks a lot in Advance.
As per your suggestion, tried to insert data into Master & Detail table using
Oracle OCI stage by checking Enable Transaction Grouping property in Transaction Handling tab.
However i am facing issues while loading data into master table.
For Example
Source Data :
col1 Col2 Col2
1 aa bb
2 bb cc
Master Table : Table1
Detail Table : Table2
In Transformer, condition used for Master Table link is
trim(Source col1)<>trim(lkp col1)
condition for Detail link is
trim(Source col1)=trim(lkp_col1)
Before first run there is no data in both Master & Detail table.
However, For First time run Data is not populated in the Master table even though Link contrain is satisfied.
Can you please suggest to how to handle this issue
Thanks a lot in Advance.
Thanks & Regards
Madhuker
Madhuker
Hi,
Source is Flat File and Look up is based on Master Table(Key column col1).
Before 1st run, lookup contains no records. Therefore in 1st run all source records has to be loaded in the Master Table (by satisfying Master Table link condition). Again in 2nd run, if the source records exists in the master table then it has to load in the Detail table, also if there are any new records in the 2nd run source file then those records has to be loaded in the master table.
Thanks in Advance.
Source is Flat File and Look up is based on Master Table(Key column col1).
Before 1st run, lookup contains no records. Therefore in 1st run all source records has to be loaded in the Master Table (by satisfying Master Table link condition). Again in 2nd run, if the source records exists in the master table then it has to load in the Detail table, also if there are any new records in the 2nd run source file then those records has to be loaded in the master table.
Thanks in Advance.
Thanks & Regards
Madhuker
Madhuker
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am