Alternative way

[chulett]

I was responding to your concerns that if you had gaps in the sequence that those gaps would somehow lead to duplicates being generated in subsequent runs, which is... wrong.

We do update one table with latest Seq Number+no.of records in the present run.

That's your problem. You should be updating it with the maximum sequence used in the 'present run'.

[Devendrudu]

Use this formula.

[@Partition number + (@INROWNUM-1)*@NUMBER PARTITIONS+1]

[prasad v]

Use this formula.

[@Partition number + (@INROWNUM-1)*@NUMBER PARTITIONS+1]

It does n't work

[priyadarshikunal]

I don't understand why you require anything like that.

The few points mentioned by other posters says it all.
1. Surrogate keys are just to maintain uniqueness and it should not matter whether there is a gap or not.
2. In case its absolutely required, which I cannot justify,
a) use surrogate key generator with block size as 1.
b) use database sequence with cache size as 1 if its a database.
c) utilize @OUTROWNUM while running transformer in sequential mode.

[jwiles]

Regarding 2a and 2b, don't use these options with when processing high quantities of data (millions of rows) unless you feel you absolutely have to. Your job performance will likely be severely degraded.

Regards,

[priyadarshikunal]

Yes, that is price for the absolute need of generating surrogate keys without GAP. I had 1 situation earlier where I had to do this on customer requirement but that was only for Job Control Purpose and not related to data load. Like for Audit and control purpose for every run we used to generate like 5 records per day, run ids of 5 modules. In that case it was a workable solution. but not always. IMO.