SQL Server begin count per row based on defined starting value?-CodePudding

I am trying to provide a ID number to a column based on the last ID in said column.

EDIT: I understand there are better ways. We cannot alter this table to use a properly sequenced column so we have to make due with providing the number in the query.

For example I can use this query to get my last ID:

SELECT MAX(ID)
FROM TABLE

Results:

If I use 1 in the query then I get my starting number for the next ID:

SELECT MAX(ID)   1
FROM TABLE

Results:

I need to be able to add this to a INSERT INT0/SELECT statement so I start at the resulting number and continue the count per row provided.

For example:

-- I know this doesn't work I do not know what would here.
Select ID (16685), * FROM QUERY RESULTS

Resulting in something like:

16685, Data, Data, Data
16686, Data, Data, Data
16687, Data, Data, Data
16688, Data, Data, Data
16689, Data, Data, Data
...

I am not sure how I can provide this value.

The column in question is a SUDO ID column that was not set up properly to increment on its own so we have to provide the numbers ourselves.

Note that there will only ever be one insert per day and we are not worried about concurrent inserts happening at once.

CodePudding user response：

Given your next ID is already determined eg select @MaxId = max(Id) from <table> you could use row_number. I'd suggest at least using this in a serializable transaction for safety.

Select row_number() over(order by <ordering criteria>)   @MaxId, *... 
FROM QUERY RESULTS

CodePudding user response：

DECLARE @MaxId INT
SELECT @MaxId = MAX(Id)
FROM MyTable
-- in order by you can set every you want
SELECT @MaxId   ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS LastId
FROM MyTable
/*
I think it is what you want
*/