I think this is horrible advice. The customer number is an identifier for a customer (I assume), and not a magnitude or quantity of some attribute. Therefore, according to...

Something like this maybe

;with
SiteInfo(SiteId, SiteName, JobId, Grade) as (
    select 1, 'aaa', 1, 'a'
    union all
    select 1,...

To see more about what Jonathan's suggesting maybe have a look at this page:

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-indexes-with-included-columns

It could be looked at like there're 2 ways to "include" columns in an index: 1) as...

drop table if exists #tblRecorddetails;
go
create table #tblRecorddetails(
  Recordid          int,
  Userid      ...

drop table if exists #temp;
go
create table #temp (
  userid int , rid varchar(10), value varchar(100));

insert #temp(userid,rid,value) values
(1,'D01','3'),
(1,'D01','4'),
(2,'C01','hey'),
(2,'C01','1');

with t_cte(userid,rid,value) as (
    select
  ...

Maybe this could work too

select 
  coalesce(ph.username, (select Message from itrm_itbf.temp.security))
from
  ITRM_ITBF.[PMD].[PMD_HR] ph
 join
  ITRM_ITBF.[Dflt].[MANUAL_LSV_PERMISSIONS] mlp on ph.username=mlp.username
      ...

Instead of returning the SSN (which is being sent to the procedure already) could you return COUNT(*)?  Count always returns a value.

It's data obfuscation project, thus no strict rules what value should be, rather it should be random. Each row should have its own value. When we are out of...

It randomly picks one of the words

;with data_cte(which_word) as (
   select 'some' union all select 'word' union all select 'here')
select top(1) 
  which_word
from
 ...

This is a beginning and it might get the OP past the id assignment issue.

drop table if exists #src_data;
go
create table #src_data(
  EmpId    ...

Now while i was trying to implement merge, problem is how to assign new identifier value for new record (it should be max value +1 , and this column...

Right there with you.  I wouldn't bother on that other thread, though.  That subject keeps cropping up over and over and over and some of the participants do too. ...

Jeff are the results reversed?  It lists FORMAT d14 method as 802ms.  Maybe copy/paste gone off?

And, please... for the love of your servers and your customers, stop all the...

@scdecade - your solution will not work for the OP.  The trim function is not available on SQL Server 2016 and would need to be replaced with a rtrim(ltrim(...)). ...

If using nested replace then this could be done in the select list.  What if the list of replacements is variable?

select 
  ltrim(rtrim(replace(replace(replace(replace(replace(Employee, '<td><br>', '||'), '<td>',...