Apologies if this is a duplicate question, but i can't find a specific example of my requirement.
I need to replicate the oracle sequence object, ie something perisistent in the database that guarantees unique id for concurrent access
Specifically we need to generate a unique value (does not need to be sequential) during insert into a temporary staging table, which populates other staging tables and eventually the target table. The value must be unique across the target table (so can't use identity column as we need the unique value when we insert in the temp table)
Multiple users are running the same code, possibly concurrently, so the unique value must be persisted (ie can't use analytic functions in the insert to generate unique values for only the result set in the current session)
We could have a sequence table, that stores sequence_name and current_value, and have a funtion that manages concurrent access, iterates and returns the value. But calling this for each row in a large insert feels inefficient
Just wondering how other people have solved this?
have you looked into queue tables?
i honestly don't know much about them, but i know they're meant for FIFO processing and have a default timestamp column that records when a row is inserted, could maybe use that as your unique identifier (or at least generate one from that)
still trying to wrap my head around what you're asking, but this is the first thing i thought of. may or may not be relevant.
@tstrick4: Queue Tables are probably a wrong way because they should be used only for small amounts of rows and SELECT AND CONSUME processes row-by-row.
@crispo3: You don't need to assign sequence values row-by-row.
Do a COUNT of the staging table, increase the sequence by that number (including commit) and then assign SequenceStartValue + ROW_NUMBER