Tuesday, December 31, 2013

Most efficient way to get next transaction number SQL -- without using IDENTITY

Basically I am trying to get the next batch number and update the value.

DECLARE @OutputTbl TABLE (BatchNumber INT)

UPDATE TableA
SET     BatchNumber = BatchNumber + 1
OUTPUT  INSERTED.BatchNumber INTO @OutputTbl

SELECT BatchNumber FROM @OutputTbl


References
1. Using UPDATE OUTPUT http://stackoverflow.com/questions/751270/best-way-to-get-the-next-id-number-without-identity
2. Getting the OUTPUTed value http://stackoverflow.com/questions/10999396/how-do-i-use-an-insert-statements-output-clause-to-get-the-identity-value

No comments:

Post a Comment

There was an error in this gadget