DECLARE @id INT = 1,
    @Name NVARCHAR(255) = 'Name';

WITH Trg AS (SELECT Id, Name
             FROM SomeTable
             WHERE Id = @Id),
    Src AS (SELECT @Id AS Id, @Name AS Name)
MERGE Trg AS T
USING Src AS S
ON (T.Id = S.Id)
WHEN NOT MATCHED BY TARGET
    THEN
    INSERT (Id, Name)
    VALUES (S.Id, S.Name)
WHEN MATCHED
    AND NOT (T.Name = S.Name)
    THEN
    UPDATE
    SET T.Name = S.Name;

create table dbo.dict(id int not null primary key) 

create proc dbo.dict_add

create table #t(id int not null primary key) 

create table dbo.dict(id int not null primary key) 

declare @id int = 0

while 1=1
begin
	begin tran
	begin try
		/*выбираем id+1 ключ для имитации конкурентной вставки*/
		select top 1 
			@id = id+1
		from dbo.dict
		order by 
			id desc


		insert dbo.dict with(holdlock, rowlock, updlock) (
			id
		) 
		select 
			@id
		where 
			not exists (
				select *
				from dbo.dict
				where 
					id = @id
			)
		commit tran
	end try
	begin catch 
		rollback tran
		;throw 
	end catch 
end 

declare @id int = 0

while 1=1
begin
	begin tran
	begin try
		/*выбираем id+1 ключ для имитации конкурентной вставки*/
		select top 1 
			@id = id+1
		from dbo.dict
		order by 
			id desc


		insert dbo.dict  (
			id
		) 
		select 
			@id
		where 
			not exists (
				select *
				from dbo.dict with(holdlock, rowlock, updlock)
				where 
					id = @id
			)
		commit tran
	end try
	begin catch 
		rollback tran
		;throw 
	end catch 
end