Hi All, I need a help.I have Source db view VW and target table TB.My requirement is to insert the records into target table which satisfies a condition. In a query, the condition can be expressed like select * from VW where VW.id not in (select TB.id from TB); In talend job ,how can we achieve this?Can I use ' Action on data' as 'Insert Ignore' for achieving the same? Thanks in advance, Sreedevi
I guess you mean you want to insert datasets into a table which does exists there? The simplest way is to set the Action to insert or update. Unfortunately Talend does not provide a modus of only inserts but without problems if the dataaset violate the uniqueness. You can use a tMap to filter the row which already exists in the target table (main flow from your source and lookup low from your target (joined with an inner join) and the output of tMap is configured as inner join reject. In this way your send only the new datasets to your output table.
Hi All, I will explain my requirement.I have to insert records into a table,say TB1.For this,I have to select records from a view VW.Schema of view is like productId,ProductName,MFGDt.ProductId is the key column.Schema of Table is like productId,ProductName.The condition for selecting records from VW is that it should not be available in TB1 and MFGDt in the specified range. Records in VW ---------------- 1 aaa 2012-12-08 2 bbb 2012-12-26 3 ccc 2013-01-05 4 ddd 2013-01-10 5 eee 2013-01-22 Records in TB1 -------------- 1 aaa 2 bbb 3 ccc I need to select records from view which are not available in TB1 also coming in a date range like 2013-01-01 to 2013-01-31 . Selected records ----------------- 4 ddd 2013-01-10 5 eee 2013-01-22 Then insert these records into TB1.So final records in TB1 is 1 aaa 2 bbb 3 ccc 4 ddd 5 eee How can I do it with Talend? Thanks, Sreedevi
Set up a DB-input-component from VW to a tMap. This is your "main". Set a DB-input-component from TB1 to tMap. This is your "lookup". Connect the two inputs in tMap using the "Join model" as "inner join".
Create an output from the tMap to a DB-output component which inserts into TB1. Set the setting of the output in tMap for "Catch output of inner join reject" to true. This way only records in VW, which are not in TB1, will be passed out of tMap to TB1. For a detailed explanation, review the User Guide: 22.214.171.124. How to use Inner Join 126.96.36.199. Lookup Inner Join rejection