Update ID column to keep unique number
We have a table to support the next available unique number for a specific use case in our application. We thought about having a bigint column that will grow to give the next available number. Is it possible to make this column as an id column and automatically increment the same row?
I found a link on stackoverflow Using a single column table (Identity)? with similar requirements. However, the mentioned solution will introduce a new record to the table. Instead of pasting, I would like to do an update on the same record to keep track of the next available unique number.
Is it possible to use the Identity column function or would I need to extract the current value, increase it explicitly, and store it back to the same row in the same column?
I would create a Stored Procedure to start a transaction, increment the saved bigint file (not the id) and return a new value, discarding the transaction if something goes wrong.
The SP upgrade section will be like (can't test atm):
DECLARE @RET TABLE (Value BigInt)
UPDATE Mytable
SET MyField += 1
OUTPUT inserted.MyField INTO @RET
IF (SELECT MAX(Value) FROM @RET) > 0
RETURN (SELECT MAX(Value) FROM @RET)
OUTPUT statement
This will only make 1 entry to the table and return the new value. Validation will return the last value returned.
Not really sure what you are trying to achieve. Trust yours right after adding the ie id
CREATE TABLE #Temp (rowName BIGINT IDENTITY(1,1) NOT NULL)
This will create a table with a column named rowName, which is automatically incremented by one.