Setting ID value upon insert

Question

Hi

How can I set the value of an ID column to MAX(ID)+1 upon insert of a new row?

I am guessing a trigger is the best place for it?

Thanks

Regards

Answer 1

The simplest way is to probably have that ID column to be an identity column (then the ID will be incremented automatically). Having ID to be identity doesn't prevent gaps, though, so it all depends on your purpose.

The other solution would be to use sequence object. I don't use them myself, so this is the document link of how to create it

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver15

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 2

Hi

In this case sometimes I would be getting data including ID from external json so I want to keep it flexible and only want to provide ID if its NULL.

Regards

Answer 3

How can I set the value of an ID column to MAX(ID)+1 upon insert of a new row?

I am guessing a trigger is the best place for it?

I would say that depend on the context. Typically I do it in the stored procedure responsible for the INSERT.

If you go for a trigger, an INSTEAD OF trigger is the best, so that you can have the ID column as a primary key and non-nullable. But as this requires you to redo the INSERT statement it is somewhat unpalatable.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Hi Y a h y a,
Has your problem been solved? If it is solved, please mark the point that you 
think is correct as an answer. This can help others who encounter similar problems.

Best Regards
Echo

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail]