One Star

update and insert

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 !
19 REPLIES
One Star

Re: update and insert

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

Re: update and insert

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.
One Star

Re: update and insert

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?
One Star

Re: update and insert

Smiley Happy
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 !
One Star

Re: update and insert

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?
One Star

Re: update and insert

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.
One Star

Re: update and insert

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
One Star

Re: update and insert

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.
One Star

Re: update and insert

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 ?
One Star

Re: update and insert

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.
One Star

Re: update and insert

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 ?
One Star

Re: update and insert

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

Re: update and insert

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 ?
One Star

Re: update and insert

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.
One Star

Re: update and insert

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 ?
One Star

Re: update and insert

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)
One Star

Re: update and insert

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.
One Star

Re: update and insert

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 ?
One Star

Re: update and insert

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.