Getting autoincrement value in advance

Question

Hi,

I am new to SQL Server. I have a table with a primary key which is an auto increment field. Of course, it gets an automatic value when a new record is inserted.

Is it possible to know/get the next autoincrement value and assign it manually to the field that represents the primary key instead of letting SQL Server assign it automatically?

Respectfully,
Jorge Maldonado

Answer 1

Consider the Sequence Numbers (https://learn.microsoft.com/en-us/sql/relational-databases/sequence-numbers/sequence-numbers) and use a simple column instead of auto-incremented.

Answer 2

No. You should not attempt to do this. In a multi-user system, there is no guarantee you will calculate the same value as assigned.

Please see:
https://www.brentozar.com/archive/2020/11/how-to-insert-rows-and-get-their-identity-values-with-the-output-clause/

Answer 3

Hi @JORGE MALDONADO BARRERA ,
I think your demand is that you wana the sequence be like 1 2 3 100 101? If so You just need to check this link: https://www.simplilearn.com/tutorials/sql-tutorial/auto-increment-in-sql
And if you want to know the next autoincrement value, you can check the two links: https://stackoverflow.com/questions/18781701/how-do-i-get-the-next-auto-increment-value-for-a-sql-table-and-use-it https://www.folkstalk.com/2022/09/get-the-next-auto_increment-value-mysql-with-code-examples.html
If I misunderstand your meaning , pls let me know.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"

Answer 4

it manually to the field that represents the primary key instead of letting SQL Server assign it automatically?

And what would that be good for; it contradict the concept of autoincrement

I am new to SQL Server

Then you should first start learning SQL.

it manually to the field

It's possible by using SET IDENTITY_INSERT