Add a new row in to grouped table if ID not exit in SQL Server

Question

I have a table Table1 with more than 1000 rows, and it has more than 30 columns. I show only the columns of interest: ID, relatedID, Name.

ID is the same when I have the same group, relatedID is different one person from other and Name is the same in all rows that is group name.

Here is an example for what my data looks like:

ID	relatedID	Name
1234	23	office
1234	56	office
1234	87	office
999	1	office
999	58	office
876	23	office

Now I want to add one person in all ID group if the person (relatedID) does not exist, other column will stay the same no change in them just a new row of a new relatedID if this relatedID does not exist.

My result should be like:

ID	relatedID	Name
1234	23	office
1234	56	office
1234	87	office
1234	1111	office --here
999	1	office
999	58	office
999	1111	office --here
876	23	office
876	1111	office --here

I tried to write a code that save data in a new temp table (#t) and then update my original table (Table1), but I don't know how I must continue.

SELECT     
    identifier, title,
    (FORMAT(registerDate, 'yyyy-MM-dd', 'en-US')) AS Reg_Date,  
    GetText(ID) AS relatedIDName, --this function has 1111 son input and return person 
    name
INTO 
    #t1
FROM    
    InfoHeap 
INNER JOIN
    ObjProperties op ON objectID = infoHeapID 
INNER JOIN
    InfoHeapCaseType ct ON ct.caseTypeID = InfoHeap.caseTypeID
WHERE    
    moduleID = 100 AND typeID = 199 
    AND registerDate BETWEEN '2020-01-01' AND '2022-12-31'

UPDATE #t1 
SET relatedIDName = CONCAT_WS(',', relatedIDName, 'Dejana') --1111
WHERE relatedIDName NOT LIKE '%Dejana%'

-- here I can't continue
-- INSERT into Table1
-- SELECT Distinct t1.*, relatedPropID = 1111 --Related propID 
-- WHERE relatedPropID NOT LIKE 1111

Answer 1

You can generate the required rows by using distinct as the following:

select distinct id, 1111 as relatedID, name
  from tbl_name

Then use a left join to insert only the rows that does not exists in your table as the following:

with t as
(
  select distinct id, 1111 as relatedID, name
  from tbl_name
)
insert into tbl_name
select t.id, t.relatedID, t.name
from t left join tbl_name tbl
on t.id = tbl.id and 
   t.relatedID = tbl.relatedID and 
   t.name = tbl.name
where  tbl.relatedID is null

demo