Increment max value by over a complete dataset

Question

Increment max value by over a complete dataset

GBeezy

SSCarpal Tunnel

Points: 4723
More actions
October 6, 2022 at 2:01 pm

#4096235

In a weird situation where I have limited say in the ultimate solution. Switching from a table with an identity column to a table that does not have an identity column, odd I know.
I can get the MAX(ID) with out an issue. I am having issues generating the next set of IDs for the data set at hand.
When max ID is 1250 and the dataset to be inserted has 15 rows in it, I need to generate this next 15 IDs:
1251, 1252, 1253...1265.

I, for the life of me, can not get this figured out. Everything I google says to use an identity column (I cant) or to use a sequence generator (again, I cant).

A push in the right direction here will be greatly appreciated!!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101180 More actions · Answer 1

Do you mean you can't use a SEQUENCE? I can't imagine why. I use those all the time when for whatever reason an identity is not appropriate. And there's a system proc that will let you request any number of values for a SEQUENCE from SQL. Server.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 2

Well what is the mechanism you're using to do the inserts? That would give us a better idea of what options you have.

ScottPletcher SSC Guru Points: 101180 More actions · Answer 3

If you need absolutely sequential numbers, no gaps, then you could use ROW_NUMBER() to generate a value to be added to the MAX() value determined before the INSERTs.

DECLARE @max_ID int

SELECT @max_ID = MAX(ID) FROM main_table

INSERT INTO main_table

SELECT @max_ID + ROW_NUMBER() OVER(ORDER BY datetime) AS new_ID, ...

FROM new_data

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Ken McKelvey SSCoach Points: 18890 More actions · Answer 4

ScottPletcher wrote:

DECLARE @max_ID int
SELECT @max_ID = MAX(ID) FROM main_table
INSERT INTO main_table
SELECT @max_ID + ROW_NUMBER() OVER(ORDER BY datetime) AS new_ID, ...
FROM new_data

That could produce race conditions and deadlocks. The following is awful for concurrency which is why IDs, sequences or GUIDs are normally used.

INSERT INTO main_table
SELECT X.mid + ROW_NUMBER() OVER(ORDER BY datetime) AS new_ID
    ,...
FROM new_data N
    CROSS JOIN
    (
        SELECT MAX(id) AS mid
        FROM main_table WITH (UPDLOCK, SERIALIZABLE)
    ) X;

This reply was modified 2 years, 5 months ago by Ken McKelvey.
This reply was modified 2 years, 5 months ago by Ken McKelvey.
This reply was modified 2 years, 5 months ago by Ken McKelvey.

Increment max value by over a complete dataset

Cookies on SQLServerCentral