Update existing record and insert new records using update strategy and expression transformation

Hello all.

Here I am going to explain how to insert new records and udpate existing records using udpate strategy transformation.
Scenario:  Insert & Update the records :
 the Source flat file records(csv) in to target oracle database which has already 2 records.
Requirements:
Source  : Flat file(csv file contains 5 records)
Flat file : csv file with 5 records.
Target : Oracle database.

Step1: For initial load, Create table and insert 2 rows in the target database.Here we used Oracle Database

Create table statement:

create table cust_addr (cust_id number(3),cust_addr1 varchar2(25),cust_state varchar2(25), created_dt timestamp, modified_dt timestamp) ;

Inserting records :
insert into cust_addr values(002,'20mainst', 'VA', sysdate, sysdate);
insert into cust_addr values(003,'30mainst', 'VA', sysdate, sysdate);
Step2: Create Source Flat file in csv format : Flat file should look like below:

*****************
Flat File (csv)
*****************
1,1mainst,VA
2,20mainst,VA
3,30mainst,VA
4,100mainst,VA
5,50mainst,VA
*******************
*Open Informatica power center Designer >> Tools>>Source analyzer >> Sources >> Import File>> Slect flat file from your desktop or flatfile location>> ( Now flat file is ready to use in mapping)
*Tools>> Target Designer >> Targets >>Targets from Database>> select table "cust_addr" (Now target db is ready to use in mapping)
*Mapping designer >> Drag source flatfile and target file in to mapping design

To achieve update / insert the records, we need update strategy transformation for update .
To confirm the updated records or insert the records, we need timestamp/datetime in expression transformation.
Expression Transformation:

We have following ports/column in the expression transformation : cust_addr (cust_id number(3),cust_addr1 varchar2(25),cust_state varchar2(25), created_dt timestamp, modified_dt timestamp, v_sysdate).
Here in the expression declare variaple port v_sysdate = sysdate ,  declare created_dt & modified_dt = v_sysdate as output port.

Update strategy Transformation:

Properties>Transformation attribute> Update strategy expression value is 1.

Target Definition:

*Make sure that created_dt& modified_dt data type is time stamp and make cust_id as primary key.

Create session and workflow >> seesion properties>>target definition>> Edit task>>Mapping>> properties >>attribute>>Insert, Update else insert, delete must be clicked..

In the connection properties make sure that we are selecting the right source/target.

Run the workflow..Records must be updated/inserted .

 Target table data structure Before update/insert

Target table data structure after mapping & executed the workflow