You set @MaxNo to the current max value from your table - but you don't change that value. So the first time it finds a value, it will duplicate it ad infinitum. Probably, you want to increment @MaxNo before you insert - but that's not a good idea.
Almost certainly, what you actually want to do is to use an IDENTITY value instead, and lest the DB sort it out for you:
IDENTITY (Property) (Transact-SQL) - SQL Server | Microsoft Docs[
^]