Informatica: how to get the auto-generated primary key of a table in Informatica mapping?

1,466 views

My question is very similar to the one below, but on a informatica environment:

Retrieving the index of an inserted row

Here is a brief summary of the issue: I'm trying to figure out how I can insert a row into a table and then find out what the value of the auto_incremented id column was set to so that I can insert additional data into another table. Our target is SQL server 2008. We have a table which has to be populated by informatica ETLs and the application is also using the same table - so, we can't use informatica sequence generator.

In the past when I have used Oracle database, there was a Oracle sequence generator transformation available in Informatica - but for SQL server, I am not sure.

Any solutions please?

posted Oct 17, 2014 by Amit Sharma

Share this question

Facebook Share Button

Twitter Share Button

LinkedIn Share Button

1 Answer

You could use a sequence generator with the 'Reset' flag enabled so that it begins with 1 each session run, then use a Lookup to cache the current max sequence value from the target table. Then you can predict the sequence number that SQL Server will generate when a record is inserted by adding NEXTVAL to the max value.

answer Oct 20, 2014 by Shweta Singh

Similar Questions

+3 votes

Is it possible to get the SQL that would be generated, without running the mapping?

We can see the SQL's generated by PowerCenter in the session log file once a mapping has been run. So to get these SQLs we have to run the mapping. I would like to know if there is any way to get the SQL that PowerCenter would generate without actually running the mapping, so that we can analyse it, fix it or apply relevant database indexes etc. in case of any issues.

If this is possible then please tell me how.

+2 votes

Informatica metadata table - Primary key column from target table

Couple of tables in our Database doesm't has any key. However, key was created in target table at informatica level.

Now my need is, need to identify informatica metadata table where I can find tgt table and corresponding column name marked as key.

Kindly suggest

0 votes

Informatica metadata table - Primary key column from target table

Couple of tables in our Database doesn't has any key. However, key was created in target table at informatica level.

Now my need is, need to identify informatica metadata table where I can find tgt table and corresponding column name marked as key.

Kindly suggest

+1 vote

Informatica metadata table - Primary key column from target table

Couple of tables in our Database doesm't have any key. However, key was created in target table at informatica level.

Now my need is,I want to identify informatica metadata table where I can find target table and corresponding column name marked as key.

These tables are created long back,not sure why it was created like that. We planed to add primary key at database level so to identify the primary key .I want to look for informatica metadata tables

Kindly suggest

+1 vote

How to update data in a non primary key table

I have one table - TableA. This is source and target also. Table doesn't have any primary key. I am fetching data from TableA, then doing some calculation on some fields and updating them in same tableA. Now how can I update data when it doesn't have any primary key or composite key? Second question - If joining two columns make a record unique then how can I use it in informatica?

Plz help

...