Generate Surrogate Key without using Sequence Generator

Saurav Mitra

It is possible to generate sequential surrogate key in the target table without the use of an Informatica Sequence Generator transformation. Using this option, one can avoid any gap in the sequence numbers of the surrogate key.

Sequencing Logic

The transaction table JOB_ORDER_TRANS has details about Job Orders created by the Marketing team in a product company. This transaction table has a sequence key (PK) in order to perform an update post the data load as demanded by the business. This PK is being populated via below mentioned method

Following sequencing logic uses “reusable expression and lookup transformation” coupled together to generate the Surrogate Key/Primary Key.

  • Reusable Lookup transformation gets maximum Primary Key from the target table and passes the value to the calling Reusable expression. In this case, Target table is the transaction table JOB_ORDER_TRANS.
  • Table Name and Column Name values in Reusable Lookup transformation are parameterized so that this combination can be used to generate sequences wherever required in other mappings.

Reusable Lookup

Reusable lookup has one I/P port (dummy), one Return Port to return the maximum Primary Key value and one Lookup dummy port to match with I/P dummy port.

Lookup Override used in this transformation is shown below:-


Here, $$LkpCol_Nm=JOB_ORDER_TRANS and $$LkpCol_Nm=Order_Key passed from the Parameter file.

Reusable Expression:

This Reusable expression calls the unconnected reusable lookup to get maximum Primary Key from the target table by passing dummy I/P. This lookup is called only once and repeated calls to this lookup is avoided by generating sequence in expression itself after the initial call.

The logic in the port V_LKP_MAX_PK is as follows:-


Port I_DUMMY is just a dummy port to get the link from the upstream transformation.

Mapping Snapshot:

Below picture shows the usage of reusable transformations in the mapping that loads Job Order Transaction table.

In the next article, we will learn about various challenges that we may face when implementing pass-through partitioning in this mapping