Thread: Insert Max int Type Primary Column Value + 1 - Microsoft Sql

How to Insert Max int Type Primary Column Value + 1 - Microsoft Sql

If you have a primary column of type int, you need to insert unique values for it, best approach is set the column IS IDENTITY type true with identity seed 1. That way you wont have to worry about it.

However in some cases you might not be able to do that. Recently i had to work for a large health provider in MA area. They gave me an access to the db without proper privileges, which doesn't allow me set the column IS IDENTITY true. Since i am in rush to finish i have to come up with a way i could insert unique incremental values for the int primary key column on a table.

insert into MyTable(tId)
Select IsNull((Select Max(IsNull(tId,0))+1 from MyTable),1)

Note that I have to wrap the inner subquery in a IsNull for the case when there is no rows in table.

Thanks a lot for sharing your knowledge.It is a useful tool.