-- создание таблицы T_ORG_ADDRESS
CREATE TABLE [AKUZ].[T_ORG_ADDRESS]
(
    [OrgAddressID] int NOT NULL,
    [House] varchar(8) NULL,
    [Block] varchar(8) NULL,
    [Flat] varchar(8) NULL,
    [ZIPCode] varchar(10) NULL,
    [FreeAddressString] varchar(256) NULL,
    [City] uniqueidentifier NULL,
    [Street] uniqueidentifier NULL,
    [PoliticalDistrict] uniqueidentifier NULL,
    [District] uniqueidentifier NULL,
    [Location] int NULL,
    [AOGUID] uniqueidentifier NULL,
    [HouseGUID] uniqueidentifier NULL
)
ON [PRIMARY]
GO

ALTER TABLE [AKUZ].[T_ORG_ADDRESS] ADD CONSTRAINT [PK_T_ORG_ADDRESS]
PRIMARY KEY CLUSTERED ([OrgAddressID]) ON [PRIMARY]
GO

ALTER TABLE [AKUZ].[T_ORG_ADDRESS] ADD CONSTRAINT [FK_T_CITY_T_ORG_ADDRESS_City]
FOREIGN KEY([City]) REFERENCES [AKUZ].[T_CITY] ([CityID])
GO

ALTER TABLE [AKUZ].[T_ORG_ADDRESS] ADD CONSTRAINT [FK_T_STREET_T_ORG_ADDRESS_Street]
FOREIGN KEY([Street]) REFERENCES [AKUZ].[T_STREET] ([StreetID])
GO

ALTER TABLE [AKUZ].[T_ORG_ADDRESS] ADD CONSTRAINT [FK_T_POLITICAL_DISTRICT_T_ORG_ADDRESS_PoliticalDistrict]
FOREIGN KEY([PoliticalDistrict]) REFERENCES [AKUZ].[T_POLITICAL_DISTRICT] ([PoliticalDistrictID])
GO

ALTER TABLE [AKUZ].[T_ORG_ADDRESS] ADD CONSTRAINT [FK_T_DISTRICT_T_ORG_ADDRESS_District]
FOREIGN KEY([District]) REFERENCES [AKUZ].[T_DISTRICT] ([DistrictID])
GO
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
															-- дроп колонки FullADDR2 из табличек + добавление ссылок на ORG_ADDRESS
-- AKUZ.T_CDA_PARTICIPANT_DEATH_CERT
ALTER TABLE [AKUZ].[T_CDA_PARTICIPANT_DEATH_CERT] DROP CONSTRAINT [FK_T_FULLADDR2_T_CDA_PARTICIPANT_DEATH_CERT_FullADDR2]
GO
ALTER TABLE [AKUZ].[T_CDA_PARTICIPANT_DEATH_CERT] DROP COLUMN [FullADDR2]
GO

-- AKUZ.T_CDA_PAYMENT_SOURCE_INFO
ALTER TABLE [AKUZ].[T_CDA_PAYMENT_SOURCE_INFO] DROP CONSTRAINT [FK_T_FULLADDR2_T_CDA_PAYMENT_SOURCE_INFO_FullADDR2]
GO
ALTER TABLE [AKUZ].[T_CDA_PAYMENT_SOURCE_INFO] DROP COLUMN [FullADDR2]
GO

-- AKUZ.T_CDA_SECTION_DEADPATINFO_PERINATAL
ALTER TABLE [AKUZ].[T_CDA_SECTION_DEADPATINFO_PERINATAL] DROP CONSTRAINT [FK_T_FULLADDR2_T_CDA_SECTION_DEADPATINFO_PERINATAL_DeathPlaceFullADDR2]
GO
ALTER TABLE [AKUZ].[T_CDA_SECTION_DEADPATINFO_PERINATAL] DROP COLUMN [DeathPlaceFullADDR2]
GO
-- добавление адресов
ALTER TABLE [AKUZ].[T_CDA_SECTION_DEADPATINFO_PERINATAL] ADD [BirthPlaceAddress] int NULL
GO
ALTER TABLE [AKUZ].[T_CDA_SECTION_DEADPATINFO_PERINATAL] ADD CONSTRAINT [FK_T_ORG_ADDRESS_T_CDA_SECTION_DEADPATINFO_PERINATAL_BirthPlaceAddress]
FOREIGN KEY([BirthPlaceAddress]) REFERENCES [AKUZ].[T_ORG_ADDRESS] ([OrgAddressID])
GO

ALTER TABLE [AKUZ].[T_CDA_SECTION_DEADPATINFO_PERINATAL] ADD [DeathPlaceAddress] int NULL
GO
ALTER TABLE [AKUZ].[T_CDA_SECTION_DEADPATINFO_PERINATAL] ADD CONSTRAINT [FK_T_ORG_ADDRESS_T_CDA_SECTION_DEADPATINFO_PERINATAL_DeathPlaceAddress]
FOREIGN KEY([DeathPlaceAddress]) REFERENCES [AKUZ].[T_ORG_ADDRESS] ([OrgAddressID])
GO

-- AKUZ.T_CDA_SECTION_DOCINFO_ABSENCE_CONTACTS_INFECTIOUS
ALTER TABLE [AKUZ].[T_CDA_SECTION_DOCINFO_ABSENCE_CONTACTS_INFECTIOUS] DROP CONSTRAINT [FK_T_FULLADDR2_T_CDA_SECTION_DOCINFO_ABSENCE_CONTACTS_INFECTIOUS_FullADDR2]
GO
ALTER TABLE [AKUZ].[T_CDA_SECTION_DOCINFO_ABSENCE_CONTACTS_INFECTIOUS] DROP COLUMN [FullADDR2]
GO
-- добавление адреса
ALTER TABLE [AKUZ].[T_CDA_SECTION_DOCINFO_ABSENCE_CONTACTS_INFECTIOUS] ADD [OrgAddress] int NULL
GO
ALTER TABLE [AKUZ].[T_CDA_SECTION_DOCINFO_ABSENCE_CONTACTS_INFECTIOUS] ADD CONSTRAINT [FK_T_ORG_ADDRESS_T_CDA_SECTION_DOCINFO_ABSENCE_CONTACTS_INFECTIOUS_OrgAddress]
FOREIGN KEY([OrgAddress]) REFERENCES [AKUZ].[T_ORG_ADDRESS] ([OrgAddressID])
GO

-- AKUZ.T_CDA_SECTION_DOCINFO_CHEMICAL_POISONING
ALTER TABLE [AKUZ].[T_CDA_SECTION_DOCINFO_CHEMICAL_POISONING] DROP CONSTRAINT [FK_T_FULLADDR2_T_CDA_SECTION_DOCINFO_CHEMICAL_POISONING_FullADDR2]
GO
ALTER TABLE [AKUZ].[T_CDA_SECTION_DOCINFO_CHEMICAL_POISONING] DROP COLUMN [FullADDR2]
GO
-- добавление адреса
ALTER TABLE [AKUZ].[T_CDA_SECTION_DOCINFO_CHEMICAL_POISONING] ADD [OrgAddress] int NULL
GO
ALTER TABLE [AKUZ].[T_CDA_SECTION_DOCINFO_CHEMICAL_POISONING] ADD CONSTRAINT [FK_T_ORG_ADDRESS_T_CDA_SECTION_DOCINFO_CHEMICAL_POISONING_OrgAddress]
FOREIGN KEY([OrgAddress]) REFERENCES [AKUZ].[T_ORG_ADDRESS] ([OrgAddressID])
GO

-- AKUZ.T_CDA_SECTION_DOCINFO_COUPON2
ALTER TABLE [AKUZ].[T_CDA_SECTION_DOCINFO_COUPON2] DROP CONSTRAINT [FK_T_FULLADDR2_T_CDA_SECTION_DOCINFO_COUPON2_FullADDR2]
GO
ALTER TABLE [AKUZ].[T_CDA_SECTION_DOCINFO_COUPON2] DROP COLUMN [FullADDR2]
GO

-- AKUZ.T_CDA_SECTION_MOTHINFO
ALTER TABLE [AKUZ].[T_CDA_SECTION_MOTHINFO] DROP CONSTRAINT [FK_T_FULLADDR2_T_CDA_SECTION_MOTHINFO_FullADDR2]
GO
ALTER TABLE [AKUZ].[T_CDA_SECTION_MOTHINFO] DROP COLUMN [FullADDR2]
GO

-- AKUZ.T_CDA_SECTION_NBINFO
ALTER TABLE [AKUZ].[T_CDA_SECTION_NBINFO] DROP CONSTRAINT [FK_T_FULLADDR2_T_CDA_SECTION_NBINFO_FullADDR2]
GO
ALTER TABLE [AKUZ].[T_CDA_SECTION_NBINFO] DROP COLUMN [FullADDR2]
GO

-- AKUZ.T_CDA_SECTION_WORK_MED_CERTIFICATE
ALTER TABLE [AKUZ].[T_CDA_SECTION_WORK_MED_CERTIFICATE] DROP CONSTRAINT [FK_T_FULLADDR2_T_CDA_SECTION_WORK_MED_CERTIFICATE_FullADDR2]
GO
ALTER TABLE [AKUZ].[T_CDA_SECTION_WORK_MED_CERTIFICATE] DROP COLUMN [FullADDR2]
GO
-- добавление адреса
ALTER TABLE [AKUZ].[T_CDA_SECTION_WORK_MED_CERTIFICATE] ADD [OrgAddress] int NULL
GO
ALTER TABLE [AKUZ].[T_CDA_SECTION_WORK_MED_CERTIFICATE] ADD CONSTRAINT [FK_T_ORG_ADDRESS_T_CDA_SECTION_WORK_MED_CERTIFICATE_OrgAddress]
FOREIGN KEY([OrgAddress]) REFERENCES [AKUZ].[T_ORG_ADDRESS] ([OrgAddressID])
GO

-- AKUZ.T_CDA_WORKPLACE
ALTER TABLE [AKUZ].[T_CDA_WORKPLACE] DROP CONSTRAINT [FK_T_FULLADDR2_T_CDA_WORKPLACE_FullADDR2]
GO
ALTER TABLE [AKUZ].[T_CDA_WORKPLACE] DROP COLUMN [FullADDR2]
GO
-- добавление адреса
ALTER TABLE [AKUZ].[T_CDA_WORKPLACE] ADD [OrgAddress] int NULL
GO
ALTER TABLE [AKUZ].[T_CDA_WORKPLACE] ADD CONSTRAINT [FK_T_ORG_ADDRESS_T_CDA_WORKPLACE_OrgAddress]
FOREIGN KEY([OrgAddress]) REFERENCES [AKUZ].[T_ORG_ADDRESS] ([OrgAddressID])
GO

-- AKUZ.T_HOSP_LANAM_SOCANAM_EDU
-- добавление адреса
ALTER TABLE [AKUZ].[T_HOSP_LANAM_SOCANAM_EDU] ADD [OrgAddress] int NULL
GO

ALTER TABLE [AKUZ].[T_HOSP_LANAM_SOCANAM_EDU] ADD CONSTRAINT [FK_T_ORG_ADDRESS_T_HOSP_LANAM_SOCANAM_EDU_OrgAddress]
FOREIGN KEY([OrgAddress]) REFERENCES [AKUZ].[T_ORG_ADDRESS] ([OrgAddressID])
GO


--AKUZ.T_CDA_MSE_REFERRAL
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL] DROP CONSTRAINT [FK_T_FULLADDR2_T_CDA_MSE_REFERRAL_LegalFullADDR2]
GO
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL] DROP COLUMN [LegalFullADDR2]
GO

ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL] DROP CONSTRAINT [FK_T_FULLADDR2_T_CDA_MSE_REFERRAL_RealFullADDR2]
GO
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL] DROP COLUMN [RealFullADDR2]
GO

--AKUZ.T_CDA_MSE_REFERRAL_GUARDIAN
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_GUARDIAN] DROP CONSTRAINT [FK_T_FULLADDR2_T_CDA_MSE_REFERRAL_GUARDIAN_FullADDR2]
GO
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_GUARDIAN] DROP COLUMN [FullADDR2]
GO

-- AKUZ.T_CDA_MSE_REFERRAL_SCOPORG_REPR_ORG
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SCOPORG_REPR_ORG] DROP CONSTRAINT [FK_T_FULLADDR2_T_CDA_MSE_REFERRAL_SCOPORG_REPR_ORG_FullADDR2]
GO
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SCOPORG_REPR_ORG] DROP COLUMN [FullADDR2]
GO
-- добавление адреса
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SCOPORG_REPR_ORG] ADD [OrgAddress] int NULL
GO
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SCOPORG_REPR_ORG] ADD CONSTRAINT [FK_T_ORG_ADDRESS_T_CDA_MSE_REFERRAL_SCOPORG_REPR_ORG_OrgAddress]
FOREIGN KEY([OrgAddress]) REFERENCES [AKUZ].[T_ORG_ADDRESS] ([OrgAddressID])
GO

-- AKUZ.T_CDA_MSE_REFERRAL_SECTION_EDU
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SECTION_EDU] DROP CONSTRAINT [FK_T_FULLADDR2_T_CDA_MSE_REFERRAL_SECTION_EDU_FullADDR2]
GO
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SECTION_EDU] DROP COLUMN [FullADDR2]
GO
-- добавление адреса
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SECTION_EDU] ADD [OrgAddress] int NULL
GO
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SECTION_EDU] ADD CONSTRAINT [FK_T_ORG_ADDRESS_T_CDA_MSE_REFERRAL_SECTION_EDU_OrgAddress]
FOREIGN KEY([OrgAddress]) REFERENCES [AKUZ].[T_ORG_ADDRESS] ([OrgAddressID])
GO

-- AKUZ.T_CDA_MSE_REFERRAL_SECTION_SCOPORG
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SECTION_SCOPORG] DROP CONSTRAINT [FK_T_FULLADDR2_T_CDA_MSE_REFERRAL_SECTION_SCOPORG_FullADDR2]
GO
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SECTION_SCOPORG] DROP COLUMN [FullADDR2]
GO
-- добавление адреса
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SECTION_SCOPORG] ADD [OrgAddress] int NULL
GO
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SECTION_SCOPORG] ADD CONSTRAINT [FK_T_ORG_ADDRESS_T_CDA_MSE_REFERRAL_SECTION_SCOPORG_OrgAddress]
FOREIGN KEY([OrgAddress]) REFERENCES [AKUZ].[T_ORG_ADDRESS] ([OrgAddressID])
GO

-- AKUZ.T_CDA_MSE_REFERRAL_SECTION_WORK
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SECTION_WORK] DROP CONSTRAINT [FK_T_FULLADDR2_T_CDA_MSE_REFERRAL_SECTION_WORK_FullADDR2]
GO
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SECTION_WORK] DROP COLUMN [FullADDR2]
GO
-- добавление адреса
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SECTION_WORK] ADD [OrgAddress] int NULL
GO
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SECTION_WORK] ADD CONSTRAINT [FK_T_ORG_ADDRESS_T_CDA_MSE_REFERRAL_SECTION_WORK_OrgAddress]
FOREIGN KEY([OrgAddress]) REFERENCES [AKUZ].[T_ORG_ADDRESS] ([OrgAddressID])
GO

-- AKUZ.T_CDA_MSE_REFERRAL_SOURCE_PAYMENT
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SOURCE_PAYMENT] DROP CONSTRAINT [FK_T_FULLADDR2_T_CDA_MSE_REFERRAL_SOURCE_PAYMENT_FullADDR2]
GO
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SOURCE_PAYMENT] DROP COLUMN [FullADDR2]
GO
-- добавление адреса
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SOURCE_PAYMENT] ADD [OrgAddress] int NULL
GO

ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SOURCE_PAYMENT] ADD CONSTRAINT [FK_T_ORG_ADDRESS_T_CDA_MSE_REFERRAL_SOURCE_PAYMENT_OrgAddress]
FOREIGN KEY([OrgAddress]) REFERENCES [AKUZ].[T_ORG_ADDRESS] ([OrgAddressID])
GO

-- перенос адресов из FullADDR в OrgAddress (для избавления от ссылок на FullADDR)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- создание таблички #tableOrgAddr
IF object_id('tempdb..#tableOrgAddr') IS NOT NULL DROP TABLE #tableOrgAddr
	create table #tableOrgAddr(FreeAddrString varchar(256), AOGUID uniqueidentifier, HouseGUID uniqueidentifier)

-- заполнение таблички #tableOrgAddr из таблицы T_CDA_WORKPLACE
insert into #tableOrgAddr (FreeAddrString, AOGUID, HouseGUID)
select fulladdr.AddrString, fulladdr.AOGUID, fulladdr.HouseGuid
	from akuz.T_CDA_WORKPLACE work
	join fias.T_FullAddr fulladdr on fulladdr.AddrID = work.FullADDR
	where work.FullADDR is not null and work.OrgAddress is null

-- заполнение таблички #tableOrgAddr из таблицы T_CDA_SECTION_WORK_MED_CERTIFICATE
union
select fulladdr.AddrString, fulladdr.AOGUID, fulladdr.HouseGuid
from akuz.T_CDA_SECTION_WORK_MED_CERTIFICATE medCert
	join fias.T_FullAddr fulladdr on fulladdr.AddrID = medCert.FullADDR
where medCert.FullADDR is not null and medCert.OrgAddress is null

-- заполнение таблички #tableOrgAddr из таблицы T_CDA_SECTION_DEADPATINFO_PERINATAL
union
select fulladdr.AddrString, fulladdr.AOGUID, fulladdr.HouseGuid
from akuz.T_CDA_SECTION_DEADPATINFO_PERINATAL deadpatinfo
	join fias.T_FullAddr fulladdr on fulladdr.AddrID = deadpatinfo.DeathPlaceFullADDR
where deadpatinfo.DeathPlaceFullADDR is not null and deadpatinfo.DeathPlaceAddress is null

union
select fulladdr.AddrString, fulladdr.AOGUID, fulladdr.HouseGuid
from akuz.T_CDA_SECTION_DEADPATINFO_PERINATAL deadpatinfo
	join fias.T_FullAddr fulladdr on fulladdr.AddrID = deadpatinfo.BirthPlaceFullADDR
where deadpatinfo.BirthPlaceFullADDR is not null and deadpatinfo.BirthPlaceAddress is null

-- заполнение таблички #tableOrgAddr из таблицы T_CDA_SECTION_DOCINFO_ABSENCE_CONTACTS_INFECTIOUS
union
select fulladdr.AddrString, fulladdr.AOGUID, fulladdr.HouseGuid
from akuz.T_CDA_SECTION_DOCINFO_ABSENCE_CONTACTS_INFECTIOUS absContInf
	join fias.T_FullAddr fulladdr on fulladdr.AddrID = absContInf.FullADDR
where absContInf.FullADDR is not null and absContInf.OrgAddress is null

-- заполнение таблички #tableOrgAddr из таблицы T_CDA_SECTION_DOCINFO_CHEMICAL_POISONING
union
select fulladdr.AddrString, fulladdr.AOGUID, fulladdr.HouseGuid
from akuz.T_CDA_SECTION_DOCINFO_CHEMICAL_POISONING chemPo
	join fias.T_FullAddr fulladdr on fulladdr.AddrID = chemPo.FullADDR
where chemPo.FullADDR is not null and chemPo.OrgAddress is null

-- заполнение таблички #tableOrgAddr из таблицы T_HOSP_LANAM_SOCANAM_EDU
union
select fulladdr.AddrString, fulladdr.AOGUID, fulladdr.HouseGuid
from akuz.T_HOSP_LANAM_SOCANAM_EDU socEdu
	join fias.T_FullAddr fulladdr on fulladdr.AddrID = socEdu.FullADDR
where socEdu.FullADDR is not null and socEdu.OrgAddress is null

-- заполнение таблички #tableOrgAddr из таблицы T_CDA_MSE_REFERRAL_SCOPORG_REPR_ORG
union
select fulladdr.AddrString, fulladdr.AOGUID, fulladdr.HouseGuid
from akuz.T_CDA_MSE_REFERRAL_SCOPORG_REPR_ORG reprOrg
	join fias.T_FullAddr fulladdr on fulladdr.AddrID = reprOrg.FullADDR
where reprOrg.FullADDR is not null and reprOrg.OrgAddress is null

-- заполнение таблички #tableOrgAddr из таблицы T_CDA_MSE_REFERRAL_SECTION_EDU
union
select fulladdr.AddrString, fulladdr.AOGUID, fulladdr.HouseGuid
from akuz.T_CDA_MSE_REFERRAL_SECTION_EDU edu
	join fias.T_FullAddr fulladdr on fulladdr.AddrID = edu.FullADDR
where edu.FullADDR is not null and edu.OrgAddress is null

-- заполнение таблички #tableOrgAddr из таблицы T_CDA_MSE_REFERRAL_SECTION_SCOPORG
union select fulladdr.AddrString, fulladdr.AOGUID, fulladdr.HouseGuid
	from akuz.T_CDA_MSE_REFERRAL_SECTION_SCOPORG ent
	join fias.T_FullAddr fulladdr on fulladdr.AddrID = ent.FullADDR
	where FullADDR is not null and OrgAddress is null

-- заполнение таблички #tableOrgAddr из таблицы T_CDA_MSE_REFERRAL_SECTION_WORK
union
select fulladdr.AddrString, fulladdr.AOGUID, fulladdr.HouseGuid
from akuz.T_CDA_MSE_REFERRAL_SECTION_WORK ent
	join fias.T_FullAddr fulladdr on fulladdr.AddrID = ent.FullADDR
where FullADDR is not null and OrgAddress is null

-- заполнение таблички #tableOrgAddr из таблицы T_CDA_MSE_REFERRAL_SOURCE_PAYMENT
union
select fulladdr.AddrString, fulladdr.AOGUID, fulladdr.HouseGuid
from akuz.T_CDA_MSE_REFERRAL_SOURCE_PAYMENT ent
	join fias.T_FullAddr fulladdr on fulladdr.AddrID = ent.FullADDR
where FullADDR is not null and OrgAddress is null
----------------------------------------------------------------
--добавление в t_entity_ids
if (not exists(select * from dbo.T_ENTITY_IDS where EntName = 'AKUZ.ORG_ADDRESS'))
	insert into dbo.t_entity_ids (EntName, FieldName, id) values('AKUZ.ORG_ADDRESS', 'PrimaryKey', 1) 
-- объявление переменной для проставления primary keys элементов таблицы
declare @i int = (select top 1 id from dbo.T_ENTITY_IDS where EntName = 'AKUZ.ORG_ADDRESS')
-- заполнение таблички t_org_address из #tableOrgAddr
insert into akuz.t_org_address (OrgAddressID, FreeAddressString, AOGUID, HouseGUID)
select (ROW_NUMBER() over (order by tab.FreeAddrString))+@i, tab.FreeAddrString, tab.AOGUID, tab.HouseGUID
	from #tableOrgAddr tab	
	where 
		(not exists(
		select * 
		from akuz.t_org_address
		where (FreeAddressString = tab.FreeAddrString and AOGUID = tab.AOGUID) or HouseGUID = tab.HouseGUID
		)) 
-- обновление int-айдишника в табличке T_ENTITY_IDS
update d
set d.id = (select isnull(max(OrgAddressID), 1) from akuz.T_ORG_ADDRESS)
from  dbo.T_ENTITY_IDS d where EntName = 'AKUZ.ORG_ADDRESS'
----------------------------------------------------------------
-- проставление новых адресов в сущность CDA_WORKPLACE
update ent
set ent.OrgAddress = orgaddress.OrgAddressID, ent.FullADDR = null
	from akuz.t_cda_workplace ent
		join fias.T_FullAddr fullAddr on fullAddr.AddrID = ent.FullADDR
		join akuz.t_org_address orgaddress on orgaddress.FreeAddressString = fullAddr.AddrString
		where ent.OrgAddress is null
----------------------------------------------------------------
-- проставление новых адресов в сущность CDA_SECTION_WORK_MED_CERTIFICATE
update ent
set ent.OrgAddress = orgaddress.OrgAddressID, ent.FullADDR = null
	from akuz.T_CDA_SECTION_WORK_MED_CERTIFICATE ent
		join fias.T_FullAddr fullAddr on fullAddr.AddrID = ent.FullADDR
		join akuz.t_org_address orgaddress on orgaddress.FreeAddressString = fullAddr.AddrString
		where ent.OrgAddress is null
----------------------------------------------------------------
-- проставление новых адресов в сущность CDA_SECTION_DEADPATINFO_PERINATAL (для DeathPlaceAddress)
update ent
set ent.DeathPlaceAddress = orgaddress.OrgAddressID, ent.DeathPlaceFullADDR = null
	from akuz.T_CDA_SECTION_DEADPATINFO_PERINATAL ent
		join fias.T_FullAddr fullAddr on fullAddr.AddrID = ent.DeathPlaceFullADDR
		join akuz.t_org_address orgaddress on orgaddress.FreeAddressString = fullAddr.AddrString
		where ent.DeathPlaceAddress is null
-- проставление новых адресов в сущность CDA_SECTION_DEADPATINFO_PERINATAL (для BirthPlaceAddress)
update ent
set ent.BirthPlaceAddress = orgaddress.OrgAddressID, ent.BirthPlaceFullADDR = null
	from akuz.T_CDA_SECTION_DEADPATINFO_PERINATAL ent
		join fias.T_FullAddr fullAddr on fullAddr.AddrID = ent.BirthPlaceFullADDR
		join akuz.t_org_address orgaddress on orgaddress.FreeAddressString = fullAddr.AddrString
		where ent.BirthPlaceAddress is null
----------------------------------------------------------------
-- проставление новых адресов в сущность CDA_SECTION_DOCINFO_ABSENCE_CONTACTS_INFECTIOUS
update ent
set ent.OrgAddress = orgaddress.OrgAddressID, ent.FullADDR = null
	from akuz.T_CDA_SECTION_DOCINFO_ABSENCE_CONTACTS_INFECTIOUS ent
		join fias.T_FullAddr fullAddr on fullAddr.AddrID = ent.FullADDR
		join akuz.t_org_address orgaddress on orgaddress.FreeAddressString = fullAddr.AddrString
		where ent.OrgAddress is null
----------------------------------------------------------------
-- проставление новых адресов в сущность CDA_SECTION_DOCINFO_CHEMICAL_POISONING
update ent
set ent.OrgAddress = orgaddress.OrgAddressID, ent.FullADDR = null
	from akuz.T_CDA_SECTION_DOCINFO_CHEMICAL_POISONING ent
		join fias.T_FullAddr fullAddr on fullAddr.AddrID = ent.FullADDR
		join akuz.t_org_address orgaddress on orgaddress.FreeAddressString = fullAddr.AddrString
		where ent.OrgAddress is null
----------------------------------------------------------------
-- проставление новых адресов в сущность HOSP_LANAM_SOCANAM_EDU
update ent
set ent.OrgAddress = orgaddress.OrgAddressID, ent.FullADDR = null
	from akuz.T_HOSP_LANAM_SOCANAM_EDU ent
		join fias.T_FullAddr fullAddr on fullAddr.AddrID = ent.FullADDR
		join akuz.t_org_address orgaddress on orgaddress.FreeAddressString = fullAddr.AddrString
		where ent.OrgAddress is null
----------------------------------------------------------------
-- проставление новых адресов в сущность CDA_MSE_REFERRAL_SCOPORG_REPR_ORG
update ent
set ent.OrgAddress = orgaddress.OrgAddressID, ent.FullADDR = null
	from akuz.T_CDA_MSE_REFERRAL_SCOPORG_REPR_ORG ent
		join fias.T_FullAddr fullAddr on fullAddr.AddrID = ent.FullADDR
		join akuz.t_org_address orgaddress on orgaddress.FreeAddressString = fullAddr.AddrString
	where ent.OrgAddress is null
----------------------------------------------------------------
-- проставление новых адресов в сущность CDA_MSE_REFERRAL_SECTION_EDU
update ent
set ent.OrgAddress = orgaddress.OrgAddressID, ent.FullADDR = null
	from akuz.T_CDA_MSE_REFERRAL_SECTION_EDU ent
		join fias.T_FullAddr fullAddr on fullAddr.AddrID = ent.FullADDR
		join akuz.t_org_address orgaddress on orgaddress.FreeAddressString = fullAddr.AddrString
	where ent.OrgAddress is null
----------------------------------------------------------------
-- проставление новых адресов в сущность CDA_MSE_REFERRAL_SECTION_SCOPORG
update ent
set ent.OrgAddress = orgaddress.OrgAddressID, ent.FullADDR = null
	from akuz.T_CDA_MSE_REFERRAL_SECTION_SCOPORG ent
		join fias.T_FullAddr fullAddr on fullAddr.AddrID = ent.FullADDR
		join akuz.t_org_address orgaddress on orgaddress.FreeAddressString = fullAddr.AddrString
		where ent.OrgAddress is null
----------------------------------------------------------------
-- проставление новых адресов в сущность CDA_MSE_REFERRAL_SECTION_WORK
update ent
set ent.OrgAddress = orgaddress.OrgAddressID, ent.FullADDR = null
	from akuz.T_CDA_MSE_REFERRAL_SECTION_WORK ent
		join fias.T_FullAddr fullAddr on fullAddr.AddrID = ent.FullADDR
		join akuz.t_org_address orgaddress on orgaddress.FreeAddressString = fullAddr.AddrString
		where ent.OrgAddress is null
----------------------------------------------------------------
-- проставление новых адресов в сущность CDA_MSE_REFERRAL_SOURCE_PAYMENT
update ent
set ent.OrgAddress = orgaddress.OrgAddressID, ent.FullADDR = null
	from akuz.T_CDA_MSE_REFERRAL_SOURCE_PAYMENT ent
		join fias.T_FullAddr fullAddr on fullAddr.AddrID = ent.FullADDR
		join akuz.t_org_address orgaddress on orgaddress.FreeAddressString = fullAddr.AddrString
		where ent.OrgAddress is null
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- обновление города в добавленных адресах в ORG_ADDRESS (в конце скрипта после всех сущностей скорее всего)
update orgaddr
set orgaddr.City = (select top 1 CityID from akuz.t_city where OKATO = addrobj.OKATO)
	from akuz.T_ORG_ADDRESS orgaddr
		join fias.t_fulladdr fulladdr on fulladdr.AOGUID =  orgaddr.AOGUID and orgaddr.HouseGUID = fulladdr.HouseGuid
		join fias.t_addrobj addrobj on fulladdr.AOGUID = addrobj.AOGUID
	where orgaddr.City is null and addrobj.CODE is not null

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
																					-- избавление от FullAddr
-- drop колонки FullADDR из таблицы T_CDA_WORKPLACE
ALTER TABLE [AKUZ].[T_CDA_WORKPLACE] DROP CONSTRAINT [FK_T_FULLADDR_T_CDA_WORKPLACE_FullADDR]
GO
ALTER TABLE [AKUZ].[T_CDA_WORKPLACE] DROP COLUMN [FullADDR]
GO
-- drop колонки FullADDR из таблицы T_CDA_SECTION_WORK_MED_CERTIFICATE
ALTER TABLE [AKUZ].[T_CDA_SECTION_WORK_MED_CERTIFICATE] DROP CONSTRAINT [FK_T_FULLADDR_T_CDA_SECTION_WORK_MED_CERTIFICATE_FullADDR]
GO
ALTER TABLE [AKUZ].[T_CDA_SECTION_WORK_MED_CERTIFICATE] DROP COLUMN [FullADDR]
GO
-- drop колонки FullADDR из таблицы T_CDA_SECTION_DEADPATINFO_PERINATAL
ALTER TABLE [AKUZ].[T_CDA_SECTION_DEADPATINFO_PERINATAL] DROP CONSTRAINT [FK_T_FULLADDR_T_CDA_SECTION_DEADPATINFO_PERINATAL_BirthPlaceFullADDR]
GO
ALTER TABLE [AKUZ].[T_CDA_SECTION_DEADPATINFO_PERINATAL] DROP COLUMN [BirthPlaceFullADDR]
GO

ALTER TABLE [AKUZ].[T_CDA_SECTION_DEADPATINFO_PERINATAL] DROP CONSTRAINT [FK_T_FULLADDR_T_CDA_SECTION_DEADPATINFO_PERINATAL_DeathPlaceFullADDR]
GO
ALTER TABLE [AKUZ].[T_CDA_SECTION_DEADPATINFO_PERINATAL] DROP COLUMN [DeathPlaceFullADDR]
GO
-- drop колонки FullADDR из таблицы T_CDA_SECTION_DOCINFO_ABSENCE_CONTACTS_INFECTIOUS
ALTER TABLE [AKUZ].[T_CDA_SECTION_DOCINFO_ABSENCE_CONTACTS_INFECTIOUS] DROP CONSTRAINT [FK_T_FULLADDR_T_CDA_SECTION_DOCINFO_ABSENCE_CONTACTS_INFECTIOUS_FullADDR]
GO
ALTER TABLE [AKUZ].[T_CDA_SECTION_DOCINFO_ABSENCE_CONTACTS_INFECTIOUS] DROP COLUMN [FullADDR]
GO
-- drop колонки FullADDR из таблицы T_CDA_SECTION_DOCINFO_CHEMICAL_POISONING
ALTER TABLE [AKUZ].[T_CDA_SECTION_DOCINFO_CHEMICAL_POISONING] DROP CONSTRAINT [FK_T_FULLADDR_T_CDA_SECTION_DOCINFO_CHEMICAL_POISONING_FullADDR]
GO
ALTER TABLE [AKUZ].[T_CDA_SECTION_DOCINFO_CHEMICAL_POISONING] DROP COLUMN [FullADDR]
GO
-- drop колонки FullADDR из таблицы T_HOSP_LANAM_SOCANAM_EDU
ALTER TABLE [AKUZ].[T_HOSP_LANAM_SOCANAM_EDU] DROP CONSTRAINT [FK_T_FULLADDR_T_HOSP_LANAM_SOCANAM_EDU_FullADDR]
GO
ALTER TABLE [AKUZ].[T_HOSP_LANAM_SOCANAM_EDU] DROP COLUMN [FullADDR]
GO
-- drop колонки FullADDR из таблицы T_CDA_MSE_REFERRAL_SCOPORG_REPR_ORG
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SCOPORG_REPR_ORG] DROP CONSTRAINT [FK_T_FULLADDR_T_CDA_MSE_REFERRAL_SCOPORG_REPR_ORG_FullADDR]
GO
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SCOPORG_REPR_ORG] DROP COLUMN [FullADDR]
GO
-- drop колонки FullADDR из таблицы T_CDA_MSE_REFERRAL_SECTION_EDU
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SECTION_EDU] DROP CONSTRAINT [FK_T_FULLADDR_T_CDA_MSE_REFERRAL_SECTION_EDU_FullADDR]
GO
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SECTION_EDU] DROP COLUMN [FullADDR]
GO
-- drop колонки FullADDR из таблицы T_CDA_MSE_REFERRAL_SECTION_SCOPORG
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SECTION_SCOPORG] DROP CONSTRAINT [FK_T_FULLADDR_T_CDA_MSE_REFERRAL_SECTION_SCOPORG_FullADDR]
GO
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SECTION_SCOPORG] DROP COLUMN [FullADDR]
GO
-- drop колонки FullADDR из таблицы T_CDA_MSE_REFERRAL_SOURCE_PAYMENT
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SOURCE_PAYMENT] DROP CONSTRAINT [FK_T_FULLADDR_T_CDA_MSE_REFERRAL_SOURCE_PAYMENT_FullADDR]
GO
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SOURCE_PAYMENT] DROP COLUMN [FullADDR]
GO
-- drop колонки FullADDR из таблицы T_CDA_MSE_REFERRAL_SECTION_WORK
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SECTION_WORK] DROP CONSTRAINT [FK_T_FULLADDR_T_CDA_MSE_REFERRAL_SECTION_WORK_FullADDR]
GO
ALTER TABLE [AKUZ].[T_CDA_MSE_REFERRAL_SECTION_WORK] DROP COLUMN [FullADDR]
GO