[resolved] How to insert data if not present in table ?
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
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.
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)
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.
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
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.
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!
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
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.
