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
Hi Asif I guess the job logic in tMap is not correct. Could you show us an example? What's your input data? What's your expected data? Then we can create a job for you. Regards, Pedro
Thank you so much for your attention.
Ok, here is the example. I make it very simple. If this can be done, I hope rest I can follow. There are two input tables 1) shippingCard and 2) shippingProduct are joined(in my case 9 tables to be joined) and output table is the shippingReport table. shippingCardId field is used to join shippingProduct and shippingCard Tables.
Table shippingCard shippingCardId(pk);shippingCardNo;shippingStatus
Table shippingProduct shippingProductId(pk);shippingCardId(fk);productGroup;billingmethod;
Output Table; shippingReport shippingReportId(pk);shippingCardId,shippingProductId,shippingCardNo;productGroup,billingMethod;
CASE1; Create shipping Card shippingCardId(pk);shippingCardNo;shippingStatus
1;BC101;ACTIVE
output in shippingReport Table: shippingReportId;shippingCardId,shippingProductId,shippingCardNo;productGroup,billingMethod;
1;1;NULL; BC101;null;null; (insert operation in output table)
CASE2; Create shipping Product shippingProductId;shippingCardId;productGroup;billingmethod;
1;1;AIR;CASH
Output: shippingReportId;shippingCardId,shippingProductId,shippingCardNo;productGroup,billingMethod;
1;1;1; BC101;ACC;CASH (update operation in output table)
CASE3 ; Create another shipping Product (insert operation in output table)
shippingProductId;shippingCardId;productGroup;billingmethod;
2;1;ACC;CASH
Output: shippingReportId;shippingCardId,shippingProductId,shippingCardNo;productGroup,billingMethod;
1;1;1; BC101;AIR;CASH
2;1;2;BC101;ACC;CASH
CASE4 ; Update productGroup from CASH TO CHEQUE shippingProductId;shippingCardId;productGroup;billingmethod;
2;1;ACC;CHEQUE
Output: shippingReportId;shippingCardId,shippingProductId,shippingCardNo;productGroup,billingMethod;
1;1;1; BC101;AIR;CASH
2;1;2;BC101;ACC;CHEQUE
Thanks.
Asif
Hi
You might create a job as the following images.
Case 4: you'd better regard shippingCardId and shippingProductId as combination key for update.
Regards,
Pedro
I tried you example but cannot make it work. Please see images.
I have created two rows in bookingCard Table.Run the job. Successfully created two lines in shippingReport.
shippingCardId shippingCardNo shippingStatus
1 BC101 ACTIVE
2 BC102 ACTIVE
Then created one line in shippingProduct.
shippingProductId shippingCardId productGroup billingMethod
1 1 AIR CASH
Hi Asif The reason that you got duplicate key is because Numeric.sequence("s1",1,1) starts from 1 every time and there are some rows in your DB table already. You might save the value of last primary key in DB table into a delimited file and extract it every time you run the job. Numeric.sequence("s1",context.lastPK,1) Regards, Pedro
Hello Pedro,
Would you please help , how can I add delete operation along with insert and update operation.
scenerio:
Source table has data deleted -> target table will also delete the same data.
FYI, at this time, our migration project job, tMap has match model->Unique match, join model->Inner Join and ForInsert Catch lookup inner join reject=true and ForUpdate, Catch lookup inner join reject=false.
These works fine. Now suddenly situation arises, I need to implement delete operation with this condition. What can the tmap settings.
Thanking you.
Asif
Hi Asif You would see the option "delete" of "Action on data" of any DB output component. Plus: if you want to delete records, the table needs to have primary key. Regards, Pedro
Hi Asif I don't understand. Let's say you want to delete data in mysql. If you want to delete a record in one table, just tell tmysqlOutput the id which you want to delete. Catch output reject = ? Catch lookup inner join reject = ? These two options depend on your own job logic. Regards, Pedro
