-- create table variable for testing
DECLARE @usertest TABLE(userid VARCHAR(50))

DECLARE @id VARCHAR(50)
DECLARE @newid VARCHAR(50)
DECLARE @count INT

-- test data
INSERT INTO @usertest
	(
	userid
	)
	SELECT
		'user'
	UNION ALL
	SELECT
		'user1'
	UNION ALL
	SELECT
		'user2'

-- return existing test data
SELECT * FROM @usertest 
		
SET @id = 'user'
SET @count = 1
SET @newid = @id

-- get next id
WHILE EXISTS (SELECT 1 FROM @usertest WHERE userid = @newid)
	BEGIN
		SET @newid = @id + CONVERT(VARCHAR(40), @count)
		
		SET @count = @count + 1
	END  

-- got newest id insert it
INSERT INTO @usertest 
	(
	userid
	) 
VALUES 
	(
	@newid
	)
	
SELECT * FROM @usertest