the best way to deal with this is to make the column an identity one so that values will get incremented automatically
If you want to simulate it,use
DECLARE @MaxID int
SELECT @MaxID=MAX(CustomerNumber)
FROM Customers
SELECT COALESCE(@MaxID,0) + 1 AS NextIDValue
please be aware that for batch insertions you've to generate bunch of new id values and above method wont work.
For that you need to make use of a temporary table with identity column itself.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Need help with sequence
Posted - 01/29/2013 : 02:36:59
