Five Stars

Insert new record if not exist in database

Hi Friends,

 

i need help i want to insert some data in table but before insert i want to check if the column name has the same record or not, and if the record exist don't insert it and if not exist insert new record

 

Thnak you

1 ACCEPTED SOLUTION

Accepted Solutions
Twelve Stars TRF
Twelve Stars

Re: Insert new record if not exist in database

Here is an example for solution #1.

Capture.PNG

POS Dynamic contains 2 records (1 new + 1 existing into POS Final).

Here is the tMap:

Capture.PNG

Make attention to the Join Model for the lookup, it must be set to "Inner join" (2nd yellow line on left side) and to the Catch lookup inner join reject parameter which must be set to "true" 'yellow line on right side).

As you can see, the result contains only 1 records, which will go to the destination (tLogRow for the example):

Starting job test at 17:51 25/10/2017.

[statistics] connecting to socket on port 3505
[statistics] connected
value 4|value 5|value 6
[statistics] disconnected
Job test ended at 17:51 25/10/2017. [exit code=0]

TRF
4 REPLIES
Twelve Stars TRF
Twelve Stars

Re: Insert new record if not exist in database

Hi,

 

Solution #1 : read PosFinal dataas a lookup for a tMap with inner join on Name field. Get rejected records as candidates for insert.

Solution #2 : place an unique index on your PosFinal table (in the database) and try to insert all the records from PosDynamic; existing records will be automagically rejected.

 

Hope this helps.


TRF
Five Stars

Re: Insert new record if not exist in database

Can please make exemple for me, i'm new in talend thanks you

Twelve Stars TRF
Twelve Stars

Re: Insert new record if not exist in database

Here is an example for solution #1.

Capture.PNG

POS Dynamic contains 2 records (1 new + 1 existing into POS Final).

Here is the tMap:

Capture.PNG

Make attention to the Join Model for the lookup, it must be set to "Inner join" (2nd yellow line on left side) and to the Catch lookup inner join reject parameter which must be set to "true" 'yellow line on right side).

As you can see, the result contains only 1 records, which will go to the destination (tLogRow for the example):

Starting job test at 17:51 25/10/2017.

[statistics] connecting to socket on port 3505
[statistics] connected
value 4|value 5|value 6
[statistics] disconnected
Job test ended at 17:51 25/10/2017. [exit code=0]

TRF
Five Stars

Re: Insert new record if not exist in database

Thank you so much