Informatica: Update Target Table with Non-Key Columns

Update Target Table with Non-Key  Columns.

 

Update Strategy transformation will update/insert/reject operation based on the Key defined in target table. Integration service will check for key column to do the update operation if we are using Update Strategy transformation in our mapping. But in our real-time project it can be required to update the target table based on Non-Key column also.

Lets take an example, here TEST_DS is our source table having the below data:

EMP_NUM	EMP_NAME
100	Sudi
200	Jayak

 

And TEST_D table is our target table  having the below data.

 

ROW_WID	EMP_NUM	EMP_NAME	PHONE_NUMBER
1	100	Sudipta C	9901
2	100	Sudipta C	9900
3	200	JK	8000
4	200	JK	8001

 

Now our requirement is to update the TEST_D table as per the TEST_DS data we have. If you notice in TEST_D table EMP_NUM is not a key column. But we need to use only EMP_NUM column for updating the TEST_D table. So our expected result should be like,

ROW_WID	-EMP_NUM	EMP_NAME	---PHONE_NUMBER
1	100	Sudi	9901
2	100	Sudi	9900
3	200	Jayak	8000
4	200	Jayak	8001

 

 

This can be handled using the “Update Override” property in target table.

 

 

Lets create a simple mapping to update the TEST_D table using Update Strategy transformation.

 

 

Now, open the target table and put your own update statement to update the target table.

 

 

Syntax to write SQL statement :

 

 

Create the Session and Workflow and run the mapping.