how to insert and update from multiple join table to single table
Hi,
Is there any way to migrate data from multiple join table to single table using insert and update operation.
Notes:
database:MSSQL 2008 R2
Sample Input Table
Table bookingCard (bookingCardId(PK), bookingCardNo,bookingStatus);
Table bookingProduct(bookingProductId(PK), bookingCardId, productGroup);
Table bookingSubProduct(bookingSubProductId(PK),bookingProductID,SalesAmount);
Sample Output Table
Table BookingReport (bookingReportId(PK), bookingCardId,bookingProductId,bookingSubProductId,productGroup,SalesAmount);
I must use left join. Because there may be bookingCard but no product or subproduct. Update can be happened on bookingStatus, productGroup or SalesAmount.
I have spent quite a long time to do insert and update operation in a different way but failed. Specially problem appears on bookingReportId primary key.
I expect Talend can handle this situation. I hope someone can write the steps to achieve this.
Thanking you. Best regards.
Asif
i am extrememly sorry. let me explain you.
I have source table and target table.I read data from target table and keep in hash. Then I have tMap where source table is main and hash is lookup. Then tMap settings inner join and unique match model. tMap output link has two mssqloutput, one for insert and one for update. For Inert I have the above logic catchlookup inner join reject=true and false for update.
see the job design
Targettable -> tHashoutput
|
|
SourceTable \
\
tMap ----------tmssqloutput(forInsert - catch lookup inner join reject = true)
/ \
hashInput/ \
-------------tMssqlOutput(forUpdate - catch lookup inner join reject = false)
I wanted to know if add another link from tMap to mssqloutput for delete operation what should be the condition
catchlookup inner join reject.
Thanks.
Asif
My Source and Target table both are same. Everytime data inserted or updated in the source table my etl job insert or update same data in target table migrating from source table.
So operatios are
if source table has a new record. my etl job insert the rows to the target table.
if source table has updates some fields of any rows, etl job also update fileds of target table.
I want if source table data is deleted, etl job will also delete target table rows.
Thanks again.
asif
Hi Asif
Got you.
In fact, what you need is called Capture Data Change feature which is only supported by Talend Enterprise Edition.
For TOS, what you can do is to record all the ids before they are deleted in source table.
Regards,
Pedro
Dear pedro,
We cannot use CDC feature now. However, I set scheduler in every five minutes and using some other logic such as last modified date of each table.Insert and update operations are fine at this moment.
Is it possible to create another job to check source table ids and target table ids and then delete those ids in target table which are not found in source table. Any idea of job settings.
Thanks.
Asif
Hi Asif Yes. It's possible. Just create a new job and do inner join with the source table and target table. Then you will get the ids which have been deleted. Regards, Pedro
Hi All,
I m using TOS 5.6.1. I build the job with more inner join and outer join in tmap. when i m using that error table and output table data are not coming properly. please any one guide me can i get the all data with one error and main table
