Generate Sequence

[c341]

Hi
I'm using @INROWNUM and @OUTROWNUM ...for generating surrogate keys....
When i see the data for the first time the sequence starts from 1 and increments by 1.....
when i run the job again by clearing the existing records...the sequence strats from where it ends.....
Now i need this sequence to strat again from 1.....
Is there any way not using this system variables and generat Sequence numbers....
Thank You
c341

[ray.wurlod]

There are lots of ways.

Search this site for terms like "surrogate key", "slowly changing dimension" and "sequence" to learn about some of them.

Ultimately you have to capture your start point from the target table and increment from there.

[chulett]

When i see the data for the first time the sequence starts from 1 and increments by 1.....
when i run the job again by clearing the existing records...the sequence strats from where it ends.....
Now i need this sequence to strat again from 1.....

This is confusing to me. If you need a Sequence to start over from 1 each time, that is the behaviour you'll get when using @INROWNUM or @OUTROWNUM as the sequence generator. You seem to be saying that you are using them and yet somehow the "Sequence" is automatically picking from where it left off in the previous run. This is not possible.

As Ray mentions, there are ways to do this - but using @IN/OUTROWNUM is not one of them.