top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration

Facebook Login
Site Registration

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

+2 votes

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

0 votes

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

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.

+1 vote

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

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

Contact Us
+91 9880187415
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Karnataka INDIA.