Four Stars

Inserting just the new data

I want to know if there is any way to do the below process with talend.

evry time the job is runing, i want to insert just the new data in the source datatable .

1 ACCEPTED SOLUTION

Accepted Solutions
Twelve Stars TRF
Twelve Stars

Re: Inserting just the new data

It depends...

If you've a primiray key on these tables, insert will fail as soon as a value already exists (and only new rows will be inserted). But can be considered a dirty approach specially if you have a large number of rows in the source for only a few of new ones.

If you want to avoid rejection for existing rows, you need to join (inner join in a tMap) rows from the source with existing rows in the target (the lookup) and "catch lookup inner join reject" to identify only new rows to be inserted.

An other approach is to store the datetime of the last run somewhere (table or file) and use this information as a filter for the select query based on a column indicating the datetime the row was created is the source database.

 

Hope this helps,

 


TRF
4 REPLIES
Twelve Stars TRF
Twelve Stars

Re: Inserting just the new data

It depends...

If you've a primiray key on these tables, insert will fail as soon as a value already exists (and only new rows will be inserted). But can be considered a dirty approach specially if you have a large number of rows in the source for only a few of new ones.

If you want to avoid rejection for existing rows, you need to join (inner join in a tMap) rows from the source with existing rows in the target (the lookup) and "catch lookup inner join reject" to identify only new rows to be inserted.

An other approach is to store the datetime of the last run somewhere (table or file) and use this information as a filter for the select query based on a column indicating the datetime the row was created is the source database.

 

Hope this helps,

 


TRF
Six Stars

Re: Inserting just the new data

Hi,

I've connected the TMsSqlOutput with tmap as Lookup and connected Tmap to the same TMsSqlOutput but i've got this warnings (Aucun lien main n'est défini pour ce composant) and my job doesn't work cc.PNG

Could you help me @TRF ?

 

Twelve Stars TRF
Twelve Stars

Re: Inserting just the new data

You cannot have a tMap without a main input flow.

Here, tMap_3 is connected with a lookup (row7) and an outpout flow (sortie).

Because the link connected to a tMap is automatically considered as the main input flow, I suppose you have deleted something which was connected to the tMap_3.

If you just want to make some transformations in tMap_3, row7 should be considered as the main input flow for tMap_3.

Cut row7, then reconnect the Output_Trend component to tMap_3.

Should solve your problem.

 

Bon courage.


TRF
Six Stars

Re: Inserting just the new data

I need (lookup) to insert just new data, this is why tMap_3 is connected with a lookup (row7).

If i reconnect the Output_Trend component to tMap_3 i can't have Lookup.

 

Thanks for your help.