Clever Geek Handbook

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?

+3


source to share


2 answers


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.

+1


source


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.

+2


source






More articles:


All Articles