top button
Flag Notify
Site Registration

Updating or Inserting records into table using informatica

0 votes

have two tables ACC and ACC_Restrict. I want to check the values of the column, P_R of either tables to see whether I should either insert, update or do nothing to ACC_Restrict. If the value is active in ACC but not in ACC_Restrict then insert into ACC_Restrict if it is still active in both ACC and ACC_Restrict then do nothing. If it is not active anymore in ACC but is still in ACC_Restrict then update ACC_Restrict by updating end_date to today's date. How can I achieve this in informatica?

Edit: There has been a modification of the requirement. ACC will only contain modifications to the account of someone. So if nothing has changed in someone's account we don't see anything in ACC but that doesn't mean if he had a restriction before, it has been lifted. It's just because nothing has changed so it doesn't appear in ACC. How do I manage this?

Note: P_R is a restriction on an account. I put the end_date to a date close to infinity and when the restriction is removed from the account I update ACC_Restrict by changing end_date to present date.

enter image description here
enter image description here

posted Jan 20, 2015 by Sachin

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

Hi ,

this we can use to implement type 1 scd,


in session properties treat source row as update

in session mapping tab tick on update and update else insert

note:- the unconnected lookup is used to lookup on the target table and to derieve the max key and increment it for furthur new records key value using expression transformation..

if key value is already coming from source then no need for the looup

This article might be of interest for this:

answer Jan 21, 2015 by Shweta Singh
Similar Questions
0 votes

Design a mapping to load the first record from a flat file into one table A, the last record from a flat file into table B and the remaining records into table C?

+1 vote

In my source table data is

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

p1,    10
p2,    xyz
p3,    abc 

Table s2

p1,    20
p2,    xyz
p3,    abc 
0 votes

I am trying to get a resultset from Teradata stored Procedure to my Mapping.

The stored procedure is to hold multiple select statements and the final output is to be sent to informatica. Below is a sample of how the select statement looks like

sel 'INH1' AS QC_CODE,count(*) from Table 1 
sel 'INH2' AS QC_CODE,count(*) from Table 2   
sel 'INH3' AS QC_CODE,count(*) from table 3

I need a stored procedure that can send the output of the above query (2 columns, 3 records) to Informatica, where I can call the stored procedure in my source qualifier or through SP transformation

Any help??