CREATE TABLE [AKUZ].[F_MEDICAL_RECORD_MedicalEpisode](
	[ID] [uniqueidentifier] NOT NULL,
	[value] [uniqueidentifier] NULL,
	 CONSTRAINT [PK_F_MEDICAL_RECORD_MedicalEpisode] PRIMARY KEY CLUSTERED 
	(
		[ID] ASC
	) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [AKUZ].[F_MEDICAL_RECORD_MedicalEpisode]  WITH CHECK ADD  CONSTRAINT [FK_SPREAD_AKUZ_MEDICAL_EPISODE_MedicalEpisode] FOREIGN KEY([value])
REFERENCES [AKUZ].[T_MEDICAL_EPISODE] ([MedicalEpisodeID])
GO

ALTER TABLE [AKUZ].[F_MEDICAL_RECORD_MedicalEpisode]  WITH CHECK ADD  CONSTRAINT [FK_SPREAD_AKUZ_MEDICAL_EPISODE_MedicalRecordID] FOREIGN KEY([ID])
REFERENCES [AKUZ].[T_MEDICAL_RECORD] ([MedicalRecordID])
GO

INSERT INTO AKUZ.F_MEDICAL_RECORD_MedicalEpisode (Id, value)
SELECT medicalrecordid, medicalepisode
FROM AKUZ.T_MEDICAL_RECORD 
WHERE MedicalEpisode is not null
GO

ALTER TABLE [AKUZ].[T_MEDICAL_RECORD] DROP CONSTRAINT [FK_T_MEDICAL_EPISODE_T_MEDICAL_RECORD_MedicalEpisode]
GO

EXEC dbo.sp_AlterDropColumn '[AKUZ].[T_MEDICAL_RECORD]' , 'MedicalEpisode', 0
GO

