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.
_______________________
From Webcosmo Webmaster Forum http://www.webcosmoforums.com/databases/27236-insert-max-int-type-primary-column-value-1-microsoft-sql.html