Insert new record if not exist in database

Six 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


Accepted Solutions
Sixteen Stars TRF
Sixteen 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

View solution in original post


All Replies
Sixteen Stars TRF
Sixteen 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
Six Stars

Re: Insert new record if not exist in database

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

Sixteen Stars TRF
Sixteen 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

View solution in original post

Six Stars

Re: Insert new record if not exist in database

Thank you so much

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog