How to implement update in 'Update Strategy' transformation ? 1

[sandhu]

Please tell me how to implement update strategy transformation?
i.e. should the source table have a primary key?
What changes do I need to make in property tabs?
Please explain the steps one by one.
I have recently started working in Informatica.
Thanks

 

[dinzana]

sandhu

Your source does not have to have a primary key, but there must me a port that will match with your target table primary key (pk) to perform the update. In other words you need to identify the row that is to be updated.

For example:

Source table data
===========

Account_ID Balance
111 100.00
222 100.00



Target table data (before running your session)
===========

Account_ID (pk) Balance
111 200.00
222 200.00
333 500.00


You should bring the source table Account_ID (we'll call it s_account_id) and the source table's balance (s_balance), through an update strategy and then into your target, linking s_account_id to the target's account_id port and s_balance to the target's balance port. In the condition of the update strategy you should have DD_UPDATE.

Your Target data would show this:


Target table data (after running your session)
===========

Account_ID (pk) Balance
111 100.00
222 100.00
333 500.00


I hope that this helps.

-dinzana

 

[sandhu]

Hi dinzana,

Thank you so much for a very detailed reply.
Please also tell me, that in 'update else insert' and 'update as insert', do I have to select the 'insert' checkbox in the Target properties?
And, that while doing 'update as insert', should the primary key be removed from target table, as it can give an error regarding the duplication of keys?

Thanks for your time and effort.

 

[dinzana]

Hello Sandhu,

If you want the rows from the source table to update the target's existing rows AND insert if it doesn't find a match, you should create a lookup to the target to find out which s_account_id's do match the target's account_ids.

Create a lookup to the target, in the condition of the lookup you should have s_account_id = account_id.

The output of the lookup (perhaps you would name it out_account_id) is then passed to the update strategy along with s_balance from the source.
In the condition of the update strategy, you should have IF (out_account_id is null, DD_INSERT, DD_UPDATE).

What this is saying is:

"If the result of the lookup to the target is null (no matches), then insert the row, else, update the row."


does this help?



I have additional Questions for you:


Did you want to keep a history in your target table?

Target table data with history
===========

Account_ID (pk) Balance Date
111 200.00 08/07/03
222 200.00 08/07/03
333 500.00 08/07/03
111 100.00 08/08/03
222 100.00 08/08/03
333 500.00 08/08/03


or did you just want to have current data?


-dinzana

 

[sandhu]

Hi Dinzana,

Thank you for your reply.
It really helps when someone helps you out quickly.
I think the solution you told should solve the problem.

Thanks again,

 

[ProDev]

Hi Dinzana,

I was going through your responses to this thread and found quite informtaive. Well, being a begininer to INFT I might rais few shilly questions like this.

1.) The LookUp transformation on target table (that u have mentioned), I understood the LookUp condition s_account_id = account_id. How about the output of the LookUp ? Is this an extra port you add up ? Basically, in your case, how do you get output from LokUp showing matching/non-matching record ? If you add up extra port "out_account_id", does it automatically get values NULL or NOT NULL based on matching/non-matching ?

2.)Probablely, you have explained to mantain current data in target table. Can you say about keeping a history in target table, too.


Thanks.

 

[sandhu]

Hi ProDev,

Reg your first query, you can get the output thu the account_id port.
For s_account_id, add a new port (say new_account_id) and input the source account value in it.
If 'new_account_id = account_id', then account_id will pass the value other wise NULL will be passed.

See if it helps.

Hi Dinzana,

That day I was in a bit of hurry.
So, couldnot teply to your query reg history of data in target table.
Pl. do explain about keeping the history of data in target table.

Thanks in advance.