ALTER TABLE [AKUZ].[T_OBSTETRIC_HOSPITAL_STAGE] ADD [Patient] uniqueidentifier NULL 
GO 

update ohs
set Patient = acr.PatientBase
from akuz.T_PREGNANT_DISPANSERISATION pd
join akuz.T_OBSTETRIC_HOSPITAL_STAGE ohs
on ohs.PregnantDispanserisation = pd.MedicalRecordID
join akuz.T_AMBULANCE_CARD_RECORD acr
on acr.MedicalRecordID = pd.MedicalRecordID
GO 

ALTER TABLE [AKUZ].[T_OBSTETRIC_HOSPITAL_STAGE] ALTER COLUMN [Patient] uniqueidentifier NOT NULL 
GO

ALTER TABLE [AKUZ].[T_OBSTETRIC_HOSPITAL_STAGE] ADD CONSTRAINT [FK_T_PATIENT_T_OBSTETRIC_HOSPITAL_STAGE_Patient]
FOREIGN KEY([Patient]) REFERENCES [AKUZ].[T_PATIENT] ([PatientID])
GO

ALTER TABLE [AKUZ].[T_PREGNANT_DISPANSERISATION] ADD [ObstetricHospitalStage] uniqueidentifier NULL
GO

ALTER TABLE [AKUZ].[T_PREGNANT_DISPANSERISATION] ADD CONSTRAINT [FK_T_OBSTETRIC_HOSPITAL_STAGE_T_PREGNANT_DISPANSERISATION_ObstetricHospitalStage]
FOREIGN KEY([ObstetricHospitalStage]) REFERENCES [AKUZ].[T_OBSTETRIC_HOSPITAL_STAGE] ([ObstetricHospitalStageID])
GO

update pd 
set ObstetricHospitalStage = ohs.ObstetricHospitalStageID
from akuz.T_PREGNANT_DISPANSERISATION pd
join akuz.T_OBSTETRIC_HOSPITAL_STAGE ohs
on ohs.PregnantDispanserisation = pd.MedicalRecordID
GO 

ALTER TABLE [AKUZ].[T_OBSTETRIC_HOSPITAL_STAGE] DROP CONSTRAINT [FK_T_PREGNANT_DISPANSERISATION_T_OBSTETRIC_HOSPITAL_STAGE_PregnantDispanserisation]
GO

ALTER TABLE [AKUZ].[T_OBSTETRIC_HOSPITAL_STAGE] DROP COLUMN [PregnantDispanserisation]
GO


ALTER TABLE [AKUZ].[T_OBSTETRIC_HOSPITAL_STAGE] ALTER COLUMN [PregnancyNumber] int NULL
GO
UPDATE [AKUZ].[T_OBSTETRIC_HOSPITAL_STAGE] SET [PregnancyNumber] = 0 WHERE [PregnancyNumber] IS NULL
GO
ALTER TABLE [AKUZ].[T_OBSTETRIC_HOSPITAL_STAGE] ALTER COLUMN [PregnancyNumber] int NOT NULL
GO

ALTER TABLE [AKUZ].[T_PREGNANT_DISPANSERISATION] ALTER COLUMN [PregnancyNumber] int NULL
GO
UPDATE [AKUZ].[T_PREGNANT_DISPANSERISATION] SET [PregnancyNumber] = 0 WHERE [PregnancyNumber] IS NULL
GO
ALTER TABLE [AKUZ].[T_PREGNANT_DISPANSERISATION] ALTER COLUMN [PregnancyNumber] int NOT NULL
GO

