use tempdb;
go

create table dbo.RoleGroups (id int identity unique, roles varchar(1000) primary key);
insert into dbo.RoleGroups values ('aaa, bbb');

declare @dummy int;
declare @result table (id int, roles varchar(1000));

with Request as
(
 select
  t.roles
 from
  (values ('aaa, bbb'), ('bbb')) t(roles)
)
merge into dbo.RoleGroups with (serializable) t
using Request s on s.roles = t.roles
when not matched then
 insert (roles) values (s.roles)
when matched then
 update
  set
   @dummy = 1
output
 inserted.id, inserted.roles into @result (id, roles);

select * from @result;
go

drop table dbo.RoleGroups;
go

select distinct
 rm.*
from
 Roles r join
 RoleGroups rgr on rgr.role_id = r.role_id join
 RoleGroups rgg on rgg.group_id = rgr.group_id join
 RoleMembers rm on rm.role_id = rgg.role_id 
where
 r.role_name = 'admin';

merge into [Staging].[dbo].[BridgeStaffRoles] with (serializable) as  t
using (SELECT Roles as RoleGroup
         from [Staging].[dbo].[DimStaff_Stage]
		 group by Roles) as s
on Roles = s.RoleGroup
when not matched then
 insert (Roles) values (s.RoleGroup)
 when matched then
 update
  set
   @dummy = 1
output inserted.RoleGroupKey, inserted.Roles into @result (id, Roles);

select r.id as RoleGroupKey, rol.RoleKey from @result as r
cross apply 
	(string_split (replace(replace(roles,'{',''),'}',''),',') 
	join [Staging].[dbo].[DimStaffRoles] as rol
	on value = rol.rolenameinsource 
	) 

merge into [DWH].[dbo].[BridgeStaffRoles] with (serializable) as  t
using (SELECT Roles as RoleGroup
         from [Staging].[dbo].[DimStaff_Stage]
		 group by Roles) as s
on Roles = s.RoleGroup
when not matched then
 insert (Roles) values (s.RoleGroup);
 go

 merge into [DWH].[dbo].[DimStaff] as t
 using (SELECT
       [Country]
      ,[StaffID]
      ,[FullName]
      ,rl.[RoleGroupKey]
--etc
  FROM [Staging].[dbo].[DimStaff_Stage] as st
  join [DWH].[dbo].[BridgeStaffRoles] as rl
  on st.roles = rl.roles) as s
  on t.country = s.country and t.staffid = s.staffid
when not matched then
insert ([Country]
      ,[StaffID]
      ,[FullName]
      ,[RoleGroupKey]
--etc
)
	  values 
	  (
	   s.[Country]
      ,s.[StaffID]
      ,s.[FullName]
       ,s.[RoleGroupKey]
--etc
--Далее если такая строка есть то обновляем
);