Update/Insert records using Update strategy 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 for Initial Load in Target Oracle database:

insert into cust_addr values(1,'1mainst', 'VA', sysdate, sysdate);
insert into cust_addr values(3,'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
1,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

In the Results, You can observe that 4 records are there.

1. It eliminates the the duplicate record(since cust_id declared as primary key in target) . New record has been updated .

2- New records has been inserted.
Create table sql
Target file Initial load<< Initial load target file is here
SourceBS to CSV file << Source flat file recs are here
Mapping xml file << Complete mapping xml file is here

Note: Above link works only if you have gmail account.