
declare @RoleAuthorizationGroups table(
	[RoleId] [int] NOT NULL,
	[GroupId] [int] NOT NULL,
	[Scope] [nvarchar](50) NULL
)

--'Warehouse technician',	
insert into @RoleAuthorizationGroups (roleid,groupid,scope)
select r.id,g.id,null from roles r , RoleAuthorizationGroupTypes g where r.guid='31EBDBC3-8596-4448-A063-1D462772118A' and g.guid in (
'1DB93246-E71B-4004-ACAA-F0DC4EF94100')

--'Technician',			
insert into @RoleAuthorizationGroups (roleid,groupid,scope)
select r.id,g.id,null from roles r , RoleAuthorizationGroupTypes g where r.guid='A508E668-94BC-4076-A8EE-972ADAD06E64' and g.guid in (
'1DB93246-E71B-4004-ACAA-F0DC4EF94100')

--'Support',				
insert into @RoleAuthorizationGroups (roleid,groupid,scope)
select r.id,g.id,null from roles r , RoleAuthorizationGroupTypes g where r.guid='14665214-632A-4729-B22B-892CC31B1894' and g.guid in(
'1DB93246-E71B-4004-ACAA-F0DC4EF94100')

--'Admin',				
insert into @RoleAuthorizationGroups (roleid,groupid,scope)
select r.id,g.id,null from roles r , RoleAuthorizationGroupTypes g where r.guid='62FD0F19-43D8-4F1E-B7C7-57A8D17A0131' and g.guid in (
'1DB93246-E71B-4004-ACAA-F0DC4EF94100')

--'Organization Admin',	
insert into @RoleAuthorizationGroups (roleid,groupid,scope)
select r.id,g.id,null from roles r , RoleAuthorizationGroupTypes g where r.guid='1BF4E983-EC11-4A4C-B02B-8F2F7631DAA9' and g.guid in (
'1DB93246-E71B-4004-ACAA-F0DC4EF94100')

--'System admin',			
insert into @RoleAuthorizationGroups (roleid,groupid,scope)
select r.id,g.id,null from roles r , RoleAuthorizationGroupTypes g where r.guid='0E9CB844-06B6-4AEA-A0DC-0FC17EE71BE2' and g.guid in (
'1DB93246-E71B-4004-ACAA-F0DC4EF94100')



MERGE INTO [dbo].RoleAuthorizationGroups AS TARGET
USING @RoleAuthorizationGroups AS SOURCE
	ON TARGET.roleid = SOURCE.roleid
	and TARGET.groupid= SOURCE.groupid
	WHEN MATCHED THEN    
			UPDATE SET scope=source.scope
	WHEN NOT MATCHED BY TARGET THEN   
			INSERT ( roleid,groupid,scope) VALUES (Source.roleid,Source.groupid,Source.scope)  
OUTPUT $action, SOURCE.*;
