I have a field that needs to be incremented by one to a certain number, then reset to one again. I.E. 1, 2, 3, 1, 2, 3, etc. How do I do this. It is not a key or constraint field, but rather merely identifies to which team a certain case will be assigned. Thanks in advance for your input.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Incrementing Field
- Thread starter critzos
- Start date
Just for clarification......
Are the column values now zero, or are some other number?
I gather you sort of want to update a column in a table by assigning a number to a bunch of rows, but rather than number them consecutively (1,2,3,4....), you would like to go 1,2,3,1,2,3,1,2,3....
Would this be about right?
Are the column values now zero, or are some other number?
I gather you sort of want to update a column in a table by assigning a number to a bunch of rows, but rather than number them consecutively (1,2,3,4....), you would like to go 1,2,3,1,2,3,1,2,3....
Would this be about right?
- Thread starter
- #3
Actually the column has no values right now as I am in the design phase. The column will have a value of 1,2,3,1,2,3,1,2,3 etc. Once data is added. also, as new rows are entered the increment would automatically occur. I.E. if the last row was 2 then the next would be 3, then back to 1, then 2, etc.
>>.....as I am in the design phase
Okay, I see.
Assuming that the Inserts into this table are done with a stored procedure (which is a good idea), then the question becomes "How can the SP find the next available team number?"
Two ways that perhaps come to mind are:
(1) Do some kind of summary function on the table to determine the last number assigned (might be painful if the table is large)
(2) Store the last number used in an auxiliary look-up table, where you can fetch the last number, add 1 to it for the Insert, and then update the auxiliary table again (in preparation for the next insert). Of course, also add some code to see if the next number has reached it's limit, and if so re-set.
Apart from an SP, this kind of code could also be in an Insert trigger. But I would lean towards the SP, so that everything associated with creating the new row can all be found in one spot.
Does this give you some ideas?
bp
Okay, I see.
Assuming that the Inserts into this table are done with a stored procedure (which is a good idea), then the question becomes "How can the SP find the next available team number?"
Two ways that perhaps come to mind are:
(1) Do some kind of summary function on the table to determine the last number assigned (might be painful if the table is large)
(2) Store the last number used in an auxiliary look-up table, where you can fetch the last number, add 1 to it for the Insert, and then update the auxiliary table again (in preparation for the next insert). Of course, also add some code to see if the next number has reached it's limit, and if so re-set.
Apart from an SP, this kind of code could also be in an Insert trigger. But I would lean towards the SP, so that everything associated with creating the new row can all be found in one spot.
Does this give you some ideas?
bp
- Thread starter
- #5
Do you prefer to use stored procedures as opposed to triggers? I.E. The trigger calls a stored procedure as opposed to having all the code in the trigger. Also, could I simply use a cursor to fetch the lst record and increment the column by one unless the limit is reached? Thanks for your time!! Really!
(1) My thought is that if you are following good practice, you are writing a stored procedure anyway to handle the Insert. So, being as you have the SP anyway, why not expand it to handle all the details of the Insert, instead of writing two other new objects (a trigger that calls another SP)
(You may get other folks with a different point of view here.)
(2) Cursors are quite useful in limited, particular situations where no 'set-based' solution seems apparent. But since SQL Server is optimized to process set-based solutions more efficiently, then cursors are not recommended unless absolutely necessary. Which doesn't seem to be the case here.
rdrds, etc
bp
(You may get other folks with a different point of view here.)
(2) Cursors are quite useful in limited, particular situations where no 'set-based' solution seems apparent. But since SQL Server is optimized to process set-based solutions more efficiently, then cursors are not recommended unless absolutely necessary. Which doesn't seem to be the case here.
rdrds, etc
bp
- Status
Similar threads
- Locked
- Question