topic Re: update and insert in Talend Studio

update and insert

Anonymous — Sat, 16 Nov 2024 12:28:53 GMT

Hi,
I have a record in tgt table
If a record with same id comes to tgt then i want to update the old record and insert the new record at the same time .
How can i do this ??
I have managed to insert the record but then i also want to change the data of previous record with same id !
Please help !

Re: update and insert

Anonymous — Mon, 02 Jan 2012 15:04:55 GMT

Hi,
Choose insert or update in your ouput, and define your id as the primary key.

Re: update and insert

Anonymous — Mon, 02 Jan 2012 19:20:05 GMT

That solution is only updating the record not even inserting.
To be precise.....
I have say...3 cols - id,val1,val2
when duplicate id comes then the same is inserted in the table and the previous id record is updated for col B.
How can this be done for subsequent records.

Re: update and insert

Anonymous — Mon, 02 Jan 2012 21:04:00 GMT

Ok,
First I just wanted to help (sry if my solution doesn't answer your problem....I thought it was a forum).
Second could you put an example please?

Re: update and insert

Anonymous — Tue, 03 Jan 2012 13:51:16 GMT

Sry.. i didnt mean to be rude.

I am trying SCD 2.
I which i have 4 cols : ID,VAL1,VAL_ST,VAL_END
My composite key here is ID and VAL_ST.
What i m trying is whenever a record with same id comes
the new record should have current VAL_ST ---
the old record should have VAL1 of new record
and VAL_END of new record
But whenever i run this job the new record is getting inserted but when updating it is updating the new and previous records both keeping the record unchanged with just a new VAL_END of the new record.
Please help !

Re: update and insert

ytuzet — Tue, 03 Jan 2012 14:31:08 GMT

Hi,
SCD type 2 will trace every change from the source table for the same id (if there are 3 modifications on the id you define there will be 3 rows in the SCD output)
maybe you should use SCD type 3 to have only current and previous version

can you post a screenshot of how you use you SCD component?

Re: update and insert

janhess — Tue, 03 Jan 2012 14:36:18 GMT

OK I think what you need here is a bit more complex.
You need your data input and your tgt table as lookup to a tMap. The tMap needt 2 outputs.
Match the input data to the lookup as an inner join on ID.
Output 1 will have the data from the lookup for ID and VAL_ST and data from the input data for VAL_1 and VAL_END
Output 2 will have all the data from the input data.
pass output 1 to modify the tgt table and pass output2 to input the tgt table.

Re: update and insert

ytuzet — Tue, 03 Jan 2012 14:46:23 GMT

it will not work for IDs with more than 2 occurences
if you want to track updates for specific fields SCD is what you should use

Re: update and insert

janhess — Tue, 03 Jan 2012 15:58:54 GMT

So long as the query sorts the data on descending VAL_END and the join is for first occurrence it will work.
You could just use the t{databasetype}SCD component.

Re: update and insert

Anonymous — Tue, 03 Jan 2012 18:48:55 GMT

Hi,
Thanx for all the help.
Somehow finally have got it sorted.
I have taken 3 outputs from a tmap which joins src table and target tables.
from 1st op i update the same id record
from 2nd op i insert the same id record
from 3rd op i insert completely new record (inner join reject op)
Let me know if this is the right way.. or is there any other optimized way ?

Re: update and insert

_AnonymousUser — Tue, 03 Jan 2012 21:27:04 GMT

I like what you suggested. Make sense to me.
And the "Insert or Update" for tOuputxxx component is so slow it's unbelivable.
I removed all of them and split my jobs in 2 parts : All the updates and the all the inserts. Less clean but this way batch processing is working.
10 time faster.
Regards
M.

Re: update and insert

Anonymous — Wed, 04 Jan 2012 03:54:20 GMT

I had tried "Insert or Update" for a job but that gives less control and it just somehow does not work as i was expecting.
I guess one can also use the bulk insert for the completely new records.
That could enhance the performance more.
The above suggested way does not work everytime.
i mean i m trying to maintain a history.
But the updating of the previous row does not occur everytime .And i m not able to trace the reason!!!!
Please let me know if any1 could figure it out !
What i would like to know is if im having 3 ops from a single component can i control their execution i.e can i execute 3 then 2 then 1 or 1,2,3 or any other combination ?

Re: update and insert

janhess — Wed, 04 Jan 2012 08:59:59 GMT

Why the 2nd op? If you update your insert will fail with duplicate key.

Re: update and insert

Anonymous — Wed, 04 Jan 2012 09:28:45 GMT

Key is on id and st_date so when a new entry of same id comes it has a different st_date.
But the problem i m facing is :
tMap : Inner Join is on ID and Match Model : UNIQUE MATCH

suppose Table A :
id;VAL1;VAL_ST
1; A; 1
Table B(target and history is maintained on VAL1)
id;VAL1;VAL_ST,VAL_END
This is empty in the beginning
On my first run according to the job
Data is new so it is simply inserted.
And tables look like :
id;VAL1;VAL_ST;
1; A; 1
Table B(target and history is maintained on VAL1)
id;VAL1;VAL_ST,VAL_END
1;A;1;null
Now i make changes to Table A changing its VAL_ST and VAL1
Table A
id;VAL1;VAL_ST;
1; B ; 2
Table B(target and history is maintained on VAL1)
id;VAL1;VAL_ST;VAL_END
1 ; A ; 1 ;null
1 ; B ; 2 ; 1
Now when i run the job again
The same is getting added again when i m expecting a no change in the target!!
The table looks like :
Table A
id;VAL1;VAL_ST;
1; B ; 2
Table B(target and history is maintained on VAL1)
id;VAL1;VAL_ST;VAL_END
1 ; A ; 1 ;null
1 ; B ; 2 ; 1
1 ; B ; 2 ;null
How do i avoid this ?

Re: update and insert

janhess — Wed, 04 Jan 2012 10:22:53 GMT

Doesn't look right.
After your first change you should have
id;VAL1;VAL_ST;VAL_END
1 ; A ; 1 ;2
1 ; B ; 2 ; null
Can you post a picture of your job and the tMap.

Re: update and insert

Anonymous — Wed, 04 Jan 2012 10:39:30 GMT

i m so sorry for the confusion :
i will rewrite :
Key is on id and st_date so when a new entry of same id comes it has a different st_date.
But the problem i m facing is :
tMap : Inner Join is on ID and Match Model : UNIQUE MATCH

suppose Table A :
id;VAL1;VAL_ST
1; A; 1
Table B(target and history is maintained on VAL1)
id;VAL1;VAL_ST,VAL_END
This is empty in the beginning
On my first run according to the job
Data is new so it is simply inserted.
And tables look like :
id;VAL1;VAL_ST;
1; A; 1
Table B(target and history is maintained on VAL1)
id;VAL1;VAL_ST,VAL_END
1;A;1;null
Now i make changes to Table A changing its VAL_ST and VAL1
Table A
id;VAL1;VAL_ST;
1; B ; 2
Table B(target and history is maintained on VAL1)
id;VAL1;VAL_ST;VAL_END
1 ; A ; 1 ;2
1 ; B ; 2 ; null
Now when i run the job again
The same is getting added again when i m expecting a no change in the target!!
The table looks like :
Table A
id;VAL1;VAL_ST;
1; B ; 2
Table B(target and history is maintained on VAL1)
id;VAL1;VAL_ST;VAL_END
1 ; A ; 1 ;2
1 ; B ; 2 ; null
1 ; B ; 2 ;null
How do i avoid this ?

Re: update and insert

janhess — Wed, 04 Jan 2012 11:10:52 GMT

You need to check in your output insert if the new values are the same as the old values
eg ! row1.VAL1.equals(row2.VAL1) & ! row1.VAL_ST.equals(row2.VAL_ST)

Re: update and insert

Anonymous — Wed, 04 Jan 2012 15:44:14 GMT

For that kind of operation, I would use 2 steps, linked with a 'onSubjobOk'.
First update, second insert.
I can't see how, with the existing components, you can easily perform the two actions at the same time.
This solution is pretty clear to implement and read later.

Re: update and insert

Anonymous — Wed, 04 Jan 2012 19:55:34 GMT

i will try the OnSubJobOk.
My solution i have explained in the above posts.
And when i test it ; it works fine for if i maintain a history upto 3 changes. But then after that it starts behaving unexpectedly.
I am not able to figure out the reason for it yet!!
@bcastell : i would like to know y is it not possible to maintain SCD type 2 with the above solution ?

Re: update and insert

Anonymous — Thu, 05 Jan 2012 06:12:20 GMT

Hi,
Thanx for the help and suggestions.
My problem has got solved.
I think we can have SCD type 2 with the above job mentioned.
If there is any other way(which i can bet there is) please do let me know.
I think ppl here are a bit shy when it comes to SCD !!
Please do let me know how else can an SCD 2 be implemented w/o using SCD component.