How to run multi table insertion in a single transaction?

svhari76 · Post by **svhari76** » Tue Dec 14, 2010 2:14 pm

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.

ray.wurlod · Post by **ray.wurlod** » Tue Dec 14, 2010 3:35 pm

Take the master and child links into a single ODBC stage. Within that ODBC stage set up a "grouped transaction".

madhuker · Post by **madhuker** » Fri Dec 17, 2010 2:05 am

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.

chulett · Post by **chulett** » Fri Dec 17, 2010 7:10 am

Different issue now. And you haven't explained the source or nature of this "lookup column" in your constraint. How is it derived?

madhuker · Post by **madhuker** » Fri Dec 17, 2010 9:28 am

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.

chulett · Post by **chulett** » Fri Dec 17, 2010 10:20 am

You need to check the success or failure of the lookup, not any kind of equality test. A lookup failure returns NULL values which will cause your contraints to always fail. Test for that instead.

madhuker · Post by **madhuker** » Sun Dec 19, 2010 11:41 pm

Hi Chulett,

Thanks a lot. Issue is resolved :D .

Sreenivasulu · Post by **Sreenivasulu** » Mon Dec 20, 2010 3:57 am

Thanks a lot Ray.. I never thought that we can do transaction handling in such a way using datastage. Thanks again

major · Post by **major** » Mon Dec 20, 2010 7:38 am

Thanks for the information.

Never know that we can do that in DataStage...