topic Re: [resolved] How to insert data if not present in table ? in Talend Studio

[resolved] How to insert data if not present in table ?

Anonymous — Sat, 16 Nov 2024 13:16:22 GMT

HI,
Can some tell me to insert a set of records if it is not present in the db table.
Example:
TableA
=======
Column_Key Column_value
-------------- ---------------
227 99400
226 88400
225 83000

TableB
=======
Column_Key Column_value
-------------- ---------------
227 99400

Here in the above example I need to add 226,225 from TableA to TableB. If not exists in TableB.
Pls help me at this stopper..

Re: [resolved] How to insert data if not present in table ?

Anonymous — Wed, 29 Sep 2010 01:00:28 GMT

Take two DB inputs - TableA and TableB
Create a tMap and make TableA the main input, TableB the lookup
In the Map create a new column lookupvalue that takes the value of the Column_value column from the Lookup schema (this will be null for any rows that do not match)
run the stream througha tFilterRow to remove any rows with this lookupvalue Equals null
run that output through a tMap to remove the lookupvalue from the output schema
run the result stream into TableB as a DB output with the Action of Data set to Insert (the default I think)

Re: [resolved] How to insert data if not present in table ?

Anonymous — Wed, 29 Sep 2010 07:50:10 GMT

Well If a primary key is set up on your table you dont even need to check if the record exists before, just try the insert (or maybe update or insert mode) and if i fails you ll be able to get the lines that are already in the table by dragging the reject flow of the tMySqlOutput component.

Re: [resolved] How to insert data if not present in table ?

Anonymous — Wed, 29 Sep 2010 08:55:44 GMT

Yes. I was trying to do the same. But somehow it's not working for my case.
I uploaded my screen shot to make it clear.
My concern is to:
1) row7 and row8 are mapped together to get the records by inner join. ---> this is to update the existing records in the output table. --> HERE UPDATE IS WORKING.
2) I want to get the records from row7 which are not satisfied as per my expression editor ---> this is to insert the records in to output table which were not present in the row8. ---> NOT GETTING THE RECORDS FROM ROW7 WHICH ARE NOT PRESENT IN ROW8.
Can you give me some idea whether I am doing it in right way or not.
Note: ROW7 represents TABLEA
ROW8 represents TABLEB
insert represents TABLEB
insert represents TABLEB

Re: [resolved] How to insert data if not present in table ?

Anonymous — Wed, 29 Sep 2010 12:45:17 GMT

Hmm I think that your filtering condition is useless since you're doing a "inner join" in your tMap you can get the rows that are in tableA but not in B by activating the rejects in your "insert" output. Just push the purple arrow button and remove your condition.

Re: [resolved] How to insert data if not present in table ?

Anonymous — Wed, 29 Sep 2010 20:44:25 GMT

Got it. It works as per your comments. Thx.

Re: [resolved] How to insert data if not present in table ?

_AnonymousUser — Mon, 07 Feb 2011 22:38:05 GMT

Sorry to necro this, but I'm interested in the same type of thing. However, I need this to be populated as the job runs.
What I mean is if we have:
TableA
=======
Column_Key Column_value
-------------- ---------------
227 "Long String Key"
226 "Long String Key"
225 "Long String Key"
TableB
=======
String_Id String_value
------------ ------------------
1 "Other String key"

While processing the lookup (I.E. replacing column_value with the string_id from tableB), it should see that Long String Key doesn't exist and insert it into TableB (The lookup table), but then we want subsequent lookups to see that we've now created an entry and also have the string_id 2 assigned to it.
Make sense? MS BI has their lookups structured kind of like that, I just don't know how to replicate the behavior in Talend. Thoughts? Thanks for the help!

Re: [resolved] How to insert data if not present in table ?

Anonymous — Tue, 09 Jul 2013 19:58:09 GMT

Hi Everybody,
I have a solution, which use only the tMap (actually between to schema)
The purpose here is to insert all products which are not existing in the destination table (based on the uuid).
Source (date.sale)
as_sale_id
as_product_uuid
...
Dest (dw.dim_product)
product_id (key)
product_uuid
The trick is to simulate (We need to "simulate" by using a tMap, because of the db are on two different RDMS):

select distinct(product_uuid)
from data.sale 
left join dw.dim_product
  on data.sale.product_uuid = dw.dim_product.product_uuid
where  dw.dim_product.product_uuid is null

To success, you just need to put dw.dim_product.product_id==0 as filter for the output (row2.product_id on the screen shoot)
Cheers

Re: [resolved] How to insert data if not present in table ?

Anonymous — Wed, 04 Mar 2015 07:20:05 GMT

Hi,
I Have Created a Job and Job Has Been Successfully Executed.Now I want Total Log Report of My Job to be Sent to My Mail ID.Please Help me Regarding with this.How Can Connect Components.Thank You.