October 6, 2022 at 2:01 pm
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!!
October 6, 2022 at 2:04 pm
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".
October 6, 2022 at 2:14 pm
Well what is the mechanism you're using to do the inserts? That would give us a better idea of what options you have.
October 6, 2022 at 2:17 pm
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".
October 6, 2022 at 9:08 pm
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;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply