update and insert

[Anonymous]

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 !

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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?

[Anonymous]

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 !

[ytuzet]

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?

[janhess]

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.

[ytuzet]

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

[janhess]

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.

[Anonymous]

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 ?