I know that when you set a column in ms sql to auto-generate IDs you won't be able to change the values. i'm having a problem with that restriction, because i have a table called Requisition, and for each entry, the requisition has a revision number. I've set the Requisition ID to auto-increment
so, requisition 1 rev 1 --- thats the primary key for each entry. when i would like to add a requisition 1, rev 2 mS sql does not allow me, and it just adds a new entry as Requisition 2 rev 2.
should i not use auto-increment? and should i just create my own auto-generation sequence? -- i really don't want to do that, is there any way that i could modify the RequisitionID column from time to time?
Please guide me to the right path.

Thanks very much,

9 Years
Discussion Span
Last Post by max_krauzer

I was thinking more in the line of sequences. Before when we were using Oracle DB, we used sequences that generated the IDs for us. we'll us the sequence whenever we want to increment the ID, otherwise, we'd just increment the revision.
is there something like this in MS SQL?


with se(id)
as (
select 120 as id
union all
select (id+1) 
as id from se
where id<(120+4)
select * from se
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.