declare @EhrEnt int, @Vers uniqueidentifier
select @EhrEnt = ee.EntityID from VCLib.T_ENTITIES ee where ee.EntName = 'EHR_RUS_M00002' 
select top 1 @Vers = reff.EhrRusRefBooksID from EHR.T_EHR_RUS_REF_BOOKS reff
where reff.Entities = @EhrEnt
order by Cast(SUBSTRING(reff.CurrVersion, 0 , CharIndex('.', reff.CurrVersion, 0)) as int) desc, cast(SUBSTRING(reff.CurrVersion, CharIndex('.', reff.CurrVersion, 0)+1 , len(reff.CurrVersion) - CharIndex('.', reff.CurrVersion, 0)) as int) desc

select atc.Code as atc, (select top 1 ehr.EhrRusID
						from EHR.T_EHR_RUS ehr 
						join EHR.T_EHR_RUS_M00002 a on a.EhrRusID = ehr.EhrRusID
						where ehr.CodeEHR = atc.Code and ehr.EhrRusRefBooks = @Vers
									) as newAtc
INTO #ATC
from PHARM.T_ATC atc


--T_ATC_LINK_MEDICAMENT
ALTER TABLE [AKUZ].T_ATC_LINK_MEDICAMENT DROP CONSTRAINT [FK_T_ATC_T_ATC_LINK_MEDICAMENT_Atc]
GO

EXEC sp_rename 'AKUZ.T_ATC_LINK_MEDICAMENT.Atc', 'AtcOld', 'COLUMN';
GO

ALTER TABLE [AKUZ].T_ATC_LINK_MEDICAMENT ADD [Atc] uniqueidentifier NULL
GO

update trn
set trn.Atc = tt.newAtc
from AKUZ.T_ATC_LINK_MEDICAMENT trn
join #ATC tt on tt.atc = trn.AtcOld
where trn.Atc is not null

ALTER TABLE [AKUZ].T_ATC_LINK_MEDICAMENT ALTER COLUMN [Atc] uniqueidentifier not null
GO

ALTER TABLE [AKUZ].[T_ATC_LINK_MEDICAMENT] ADD CONSTRAINT [FK_T_EHR_RUS_M00002_T_ATC_LINK_MEDICAMENT_Atc]
FOREIGN KEY([Atc]) REFERENCES [EHR].[T_EHR_RUS_M00002] ([EhrRusID])
GO

ALTER TABLE [AKUZ].T_ATC_LINK_MEDICAMENT DROP COLUMN [AtcOld]
GO


--T_TREATMENT_DISEASE_LINK_MEDICAMENT
ALTER TABLE [AKUZ].T_TREATMENT_DISEASE_LINK_MEDICAMENT DROP CONSTRAINT [FK_T_ATC_T_TREATMENT_DISEASE_LINK_MEDICAMENT_Atc]
GO

EXEC sp_rename 'AKUZ.T_TREATMENT_DISEASE_LINK_MEDICAMENT.Atc', 'AtcOld', 'COLUMN';
GO

ALTER TABLE [AKUZ].T_TREATMENT_DISEASE_LINK_MEDICAMENT ADD [Atc] uniqueidentifier NULL
GO

update trn
set trn.Atc = tt.newAtc
from AKUZ.T_TREATMENT_DISEASE_LINK_MEDICAMENT trn
join #ATC tt on tt.atc = trn.AtcOld
where trn.Atc is not null


ALTER TABLE [AKUZ].[T_TREATMENT_DISEASE_LINK_MEDICAMENT] ADD CONSTRAINT [FK_T_EHR_RUS_M00002_T_TREATMENT_DISEASE_LINK_MEDICAMENT_Atc]
FOREIGN KEY([Atc]) REFERENCES [EHR].[T_EHR_RUS_M00002] ([EhrRusID])
GO

ALTER TABLE [AKUZ].T_TREATMENT_DISEASE_LINK_MEDICAMENT DROP COLUMN [AtcOld]
GO


--T_TREATMENT_TEMPLATE_LINK_MEDICAMENT
ALTER TABLE [AKUZ].T_TREATMENT_TEMPLATE_LINK_MEDICAMENT DROP CONSTRAINT [FK_T_ATC_T_TREATMENT_TEMPLATE_LINK_MEDICAMENT_Atc]
GO

EXEC sp_rename 'AKUZ.T_TREATMENT_TEMPLATE_LINK_MEDICAMENT.Atc', 'AtcOld', 'COLUMN';
GO

ALTER TABLE [AKUZ].T_TREATMENT_TEMPLATE_LINK_MEDICAMENT ADD [Atc] uniqueidentifier NULL
GO

update trn
set trn.Atc = tt.newAtc
from AKUZ.T_TREATMENT_TEMPLATE_LINK_MEDICAMENT trn
join #ATC tt on tt.atc = trn.AtcOld
where trn.Atc is not null

ALTER TABLE [AKUZ].[T_TREATMENT_TEMPLATE_LINK_MEDICAMENT] ADD CONSTRAINT [FK_T_EHR_RUS_M00002_T_TREATMENT_TEMPLATE_LINK_MEDICAMENT_Atc]
FOREIGN KEY([Atc]) REFERENCES [EHR].[T_EHR_RUS_M00002] ([EhrRusID])
GO

ALTER TABLE [AKUZ].[T_TREATMENT_TEMPLATE_LINK_MEDICAMENT] DROP COLUMN AtcOld
GO

drop table #ATC