topic Re: Insert Update and delete in destination file in Talend Studio

Insert Update and delete in destination file

MaheswarReddy — Sun, 27 May 2018 09:28:10 GMT

Hi Team,

I have a file input and file output,

It's an every day load and i will get file everyday
I want to lookup my output file and check if there is any change in the new input file and if the record doesn't exists in the output file , we need to insert into the output file, if the row already exists , I need to update the data in the output file .

My source and target are files, So I'm stuck in the implementation part.

Can anyone help me out on this.

Thanks for your help.

Mahesh

Re: Insert Update and delete in destination file

TRF — Sun, 27 May 2018 09:57:02 GMT

If you have fields such as unique key and version number, you may just use tMap with inner join with new file as main row and previous one as lookup.
If you have to be able to detect any change value for any field you have to get the MD5 value for all the fields but the key giving the following schema: keyField, md5Field. Then you can compare both versions using a tMap with new MD5 file as main row and previous one as lookup. Use keyField for the inner join.
If key exists but md5 is not the same value, the record has been updated.
If key doesn't exist, the record is new.
If you need to detect deleted records, you need an other subjob where you main row becomes the lookup, then rejected records are deleted ones.

This link may help you https://community.talend.com/t5/Design-and-Development/How-to-generate-MD5-hash-value/td-p/43927

Re: Insert Update and delete in destination file

MaheswarReddy — Sun, 27 May 2018 10:31:20 GMT

Thanks for your quick reply,

But is there any other way to do other than writing a routine ?

Thanks,

Mahesh

Re: Insert Update and delete in destination file

TRF — Sun, 27 May 2018 10:47:35 GMT

You can also join each main row field to the corresponding one in the lookup.

You need 2 steps:

1- does the record exist in the previous file?

2- no, this is a new record. yes, have a 2nd tMap with all fields joined to each other to get changed records.

Re: Insert Update and delete in destination file

tnewbie — Wed, 05 Jun 2019 18:44:13 GMT

Hello TRF,

Can you please explain how to 'update' or 'delete' record in a flat file target.

Example

Data in Src_file :

col1,col2,col3

1,john,employee

2,mary,retired

3,sam,joining

Data in Tgt File(Active employees only):

empno.empname

1,john

2,mary

3,sam

4,Hary

5, Phil

Now that Mary has retired, i need to delete row#2, can you please help