Informatica: Converting a table into multiple target tables based on one column.

Question

In my source table data is

s_name,p_name,value
s1 ,   p1,     10
s1 ,   p2,     xyz
s1 ,   p3 ,    abc
s2 ,   p1 ,    20
s2 ,   p2 ,    xyz
s2 ,   p3 ,    abc

I want two target tables, first table is based on s_name s1, second table based on s_name s2. Both table contains contains p_name and value.

The target table data like as

Table s1

p_name,value 
p1,    10
p2,    xyz
p3,    abc 

Table s2

p_name,value 
p1,    20
p2,    xyz
p3,    abc 

Comments

thank u venkat

---

Try router transformation it should help.
http://www.tecktricks.com/router-transformation-in-informatica-with-example/

---

sorry sir,if you get s3 record updated in table know what we are doing, thats why i think router transformation isn't correct.

---

i have only single target table and occuring multiple occuring tables

Answer 1

Read the following document Creating Target Files

Read the following thread and let me know if you are not able to achieve whatever u wanted.
https://community.informatica.com/message/89759

Answer 2

As the first step let’s create the flat file definition using Target Designer. Add one new column 'FileName' using the 'Add File Name Column' button. This is the column based on the file name is dynamically changed.

Sort the data using the SORTER transformation with respect to s_name column.

Add an EXPRESSION transformation to set a boundary flag for each s_name group. In the expression transformation add following ports.
- VNEWFILEFLAG as Variable Port.
- VSTATE as Variable Port.
- NEWFILEFLAG as Output Port.
- FileName as Output Port.

And the following are the expressions for the ports.
- VNEWFILEFLAG :- IIF(s_name = VSTATE, 'N', 'Y')
- VSTATE :- s_name
- NEWFILEFLAG :- VNEWFILEFLAG
- FileName :- 'CustomerMaster' || s_name'

Based on the flag we created in the EXPRESSION transformation, we are going to split the file in the next step. We will use TRANSACTION CONTROL transformation to do the same. Set the 'Transaction Control Condition' property of the transformation with the below expression.
IIF(NEWFILEFLAG='Y',TCCOMMITBEFORE,TCCONTINUETRANSACTION)

Answer 3

Router transformation routes the rows of data into a separate output group based on conditions defined for each group.

For your example you will need two define two output groups with the following conditions:

group s1_rows - condition s_name = 's1'
group s2_rows - condition s_name = 's2'