Five Stars

[resolved] How to lookup if a database record exists or not(to get a primary key)

Hi Everyone
I am new to talend and this might be a repeat question but could not find a suitable answer so asking again.
1. I have a client data file which can have both Inserts and Updates and needs to applied to the database (SqlServer). The most relevant field in the data record is an external_client_id. 
2. I have added a file read, a map and a database output task with operation as Update and Insert.
3. I am needed to do a lookup on this external_client_id  and determine if the record exist or not. If it does exist then assign the primary key of the existing database record to the record in the talend map  so that the database operation will become an update otherwise create a new primary key from a sequence and assign that so we insert
What i have not been able to figure out is how to do this lookup? and is there any caching available for these lookups to prevent frequent database reads?
Thanks in advance
Regards
Sid
1 ACCEPTED SOLUTION

Accepted Solutions
Four Stars

Re: [resolved] How to lookup if a database record exists or not(to get a primary key)

Hi Sid,
Based on your description, get two flows from the tMap with inner join at the input lookup
- one for update having existing records
- Second for insert with new key (lookup reject)
connect two output component, one with update and another with insert..
There is an issue with this logic
- Suppose first record which was not in the lookup is sent to insert by generating new key
- if the same record comes again, then also it would be sent to insert because lookup is loaded once in default setting
A way to solve this issue is select an option inner join with reload at each row.. 
Try this way
Vaibhav
8 REPLIES
Four Stars

Re: [resolved] How to lookup if a database record exists or not(to get a primary key)

Hi Sid,
Based on your description, get two flows from the tMap with inner join at the input lookup
- one for update having existing records
- Second for insert with new key (lookup reject)
connect two output component, one with update and another with insert..
There is an issue with this logic
- Suppose first record which was not in the lookup is sent to insert by generating new key
- if the same record comes again, then also it would be sent to insert because lookup is loaded once in default setting
A way to solve this issue is select an option inner join with reload at each row.. 
Try this way
Vaibhav
Five Stars

Re: [resolved] How to lookup if a database record exists or not(to get a primary key)

Thanks a lot sanvaibhav. Worked like a charm.
Five Stars

Re: [resolved] How to lookup if a database record exists or not(to get a primary key)

Hi
How can i use the same approach for multiple lookups
Say i am loading some brand information
The input file has brandDetails|Client_ID|Client_BrandId
A sample could be
BrandDetail|ClientID1|BrandId1
BrandDetail|ClientID1|BrandId2
BrandDetail|ClientID2|BrandId1
So i need to first do a lookup for Client and then look up that the brandId exists for that client. The brandId need not be unique across Clients
I added two lookups to  map with the inner join of clientId and then pass the primary key found to the lookup of brand table (hich is gain an inner join) But everything got rejected(although the client Id exist).
Four Stars

Re: [resolved] How to lookup if a database record exists or not(to get a primary key)

Hi Siddharth,
Above approach should work for multiple lookups as well... But when it comes to multiple lookups, where condition applies for both the inner joins and when the one of the condition does not satisfy, it will reject.
Can you show actual main and lookup data to verify what is happening ?
Vaibhav
Five Stars

Re: [resolved] How to lookup if a database record exists or not(to get a primary key)

Hi Sanvaibhav
I have attached screen shots of the approach i have taken. I have a company and Brand table lookup on my map. The second screen shot shows what i am trying to achieve.
I have the client_brand_id and client_company_id as input in the data file.
I first need to do a lookup on the company table based on client_company_id and get the primary (company_id). I then need to feed this company_id and client_brand_id to the brand lookup to determine if the brand exists for a company and accordingly update or insert.
Could be that i have used a wrong job design.
Four Stars

Re: [resolved] How to lookup if a database record exists or not(to get a primary key)

Hi Sid,
I think you need to review your business logic for look-ups...
- Whether you really need to have both inner join i.e. whee clause for Brand and Company to push data forward?
- Whether going step by step i.e. having two tMaps one after another would give correct results...
- Rethink on taking left outer join and replacing value by -1 or something similar for the records which does not match with the lookup.
Please try describing your business requirement.
Thanks
vaibhav
Five Stars

Re: [resolved] How to lookup if a database record exists or not(to get a primary key)

Hi Vaibhav
The business requirement is to first do a look-up on Company (using the client company Id from the file) and make sure that the PK exist (If not reject the record to avoid FK constraint at the db)
- then supply that company PK to the brand lookup to determine if the client brand Id from the file exist to determine operation - Update or Insert
Siddharth
Four Stars

Re: [resolved] How to lookup if a database record exists or not(to get a primary key)

To have simplified approach which would also help you to debug and analyze data 
- use first tmap to lookup with company and reject records to csv 
- use another tmap and then lookup with the brand and use upsert operation based on the key column
Vaibhav