-- где есть фичи
declare @MappingTablePackage table (
    PackageType varchar(100),
    FeatureName nvarchar(100)
);

insert into @MappingTablePackage (PackageType, FeatureName)
values 
    ('DISP_DIRECT_FILE', 'DispListLoad'),
    ('DISP_DIRECT_INFO_FILE', 'DispInfoLoad'),
    ('DISP_DIRECT_INTERVIEW_FILE', 'InterviewListLoad'),
    ('DISP_DIRECT_IDENT', 'DispIdentLoad'),
	('DISP_DIRECT_FACT_FILE', 'DispFactListLoad'),
	('DN_INFO_FILE', 'loadDNInfo'),
	('DN_FACT_FILE', 'loadDN'),
	('DN_ITEM', 'loadDN'),
	('DN_IDENT', 'loadDNIdent')

declare @sql nvarchar(MAX) = '';

select @sql = @sql + 
'insert into ISZL.T_PACKAGE_TYPE_ROLES (PackageTypeRolesID, PackageType, Roles)
select 
    NEWID(),
    (select PackageType from ISZL.t_Package_type where PackageType = ''' + m.PackageType + '''),
    tr.RoleID
from VCLib.T_BASE_FEATURE_SETTINGS tbfs
join VCLib.T_ROLE_CLIENT_MODULES trcm on tbfs.RoleClientModules = trcm.RoleClientModuleID
join VCLib.T_ROLES tr on tr.RoleID = trcm.RoleID
join VCLib.T_CLIENT_MODULE_FEATURE tcmf on tcmf.ClientModuleFeatureID = tbfs.ClientModuleFeature
where tcmf.[Name] = ''' + m.FeatureName + ''';' + CHAR(10)
from @MappingTablePackage m;

exec sp_executesql @sql;

-- где нет фич
declare @MappingTableAll table (
    PackageType varchar(100),
    FeatureName nvarchar(100)
);

insert into @MappingTableAll (PackageType, FeatureName)
values 
    ('SCHET_FILE', 'AutoLoadFilesClientModule'),
	('BRANCH', 'AutoLoadFilesClientModule'),
	('DIVISION', 'AutoLoadFilesClientModule'),
	('AMOUNT_BED', 'AutoLoadFilesClientModule'),
	('FREE_BED', 'AutoLoadFilesClientModule'),
	('CANCEL_HR', 'AutoLoadFilesClientModule'),
	('CANCEL_H', 'AutoLoadFilesClientModule'),
	('HOSPREF', 'AutoLoadFilesClientModule'),
	('HOSP', 'AutoLoadFilesClientModule'),
	('MINHOSP', 'AutoLoadFilesClientModule'),
	('RECOMENDATIONS', 'AutoLoadFilesClientModule'),
	('SMO_EXPERTISE_ACT', 'AutoLoadFilesClientModule'),
	('MO_ATTACHMENT', 'AutoLoadFilesClientModule'),
	('DISP_DIRECT_GIS', 'AutoLoadFilesClientModule'),
	('ONK_CANCREGISTER', 'AutoLoadFilesClientModule'),
	('INSURED', 'AutoLoadFilesClientModule'),
	('DISP_DIRECT_FACT_FILE', 'AutoLoadFilesClientModule'),
	('APPEAL_TO_CALL_CENTER', 'CallCenterAdminClientModule')

declare @sql2 nvarchar(MAX) = '';

select @sql2 = @sql2 + 

'insert into ISZL.T_PACKAGE_TYPE_ROLES (PackageTypeRolesID, PackageType, Roles)
select 
    NEWID(),
    (select PackageType FROM ISZL.t_Package_type WHERE PackageType = ''' + m.PackageType + '''),
    trcm.RoleID
from VCLib.T_ROLE_CLIENT_MODULES trcm
join VCLib.T_CLIENT_MODULES cm on cm.ClientModuleID = trcm.ClientModuleID
where cm.ModuleType = ''' + m.FeatureName + ''';' + CHAR(10)
from @MappingTableAll m;

exec sp_executesql @sql2
