Hello,
I was able to get the following but not able to get the number to start from 900001.
DECLARE @new_seq INT=(
SELECT TOP 1
[NewNum] + 1
FROM [table] mo WITH(ROWLOCK,XLOCK,HOLDLOCK)
WHERE not([NewNum] is null) and NOT EXISTS
(
SELECT NULL
FROM [table] mi WITH(ROWLOCK,XLOCK,HOLDLOCK)
WHERE mi.[NewNum] = mo.[NewNum] + 1
)
ORDER BY
[NewNum]
);
IF @new_seq IS NULL
BEGIN SET @new_seq=900001;
END
ELSE
BEGIN SET @new_seq=@new_seq;
END
INSERT INTO dbo.[table]([NewNum]) VALUES(@new_seq);
Im trying to see which number is already there and whats missing, I would like for it to see between 900000 to 999999 but this is what current data looks like:
NewNumber
904969
904984
904999
904998
905142
905141
I can put dummy record of 900000 then the above code will work but without putting that dummy info how can i get it to work?
Hope what i put up make sense.
Thanks