select mr.MedicalRecordID, COALESCE(mr.RecordDate, mr.RecordTime, acr.PostingDate, '20240101') as NewDate
INTO #FOR_UPDATE_MR
from AKUZ.T_MEDICAL_RECORD mr
left join AKUZ.T_AMBULANCE_CARD_RECORD acr on acr.MedicalRecordID = mr.MedicalRecordID
where mr.RecordDate is null or mr.RecordTime is null 

CREATE CLUSTERED INDEX IX_FOR_UPDATE_MR ON #FOR_UPDATE_MR (MedicalRecordID);
GO

CREATE TABLE TMP_FOR_DELETE_CYCLE(
	 ID UNIQUEIDENTIFIER
	,DATEREC_NEW DATETIME
);

GO

CREATE INDEX IX_TMP_FOR_DELETE_CYCLE ON TMP_FOR_DELETE_CYCLE (ID);

GO

WHILE ((SELECT COUNT(*) FROM #FOR_UPDATE_MR) > 0)
BEGIN
	
	-- Удаляем строки из первой таблицы и вставляем во вторую
	DELETE TOP (10000) Main
	OUTPUT DELETED.MedicalRecordID, DELETED.NewDate INTO TMP_FOR_DELETE_CYCLE (ID, DATEREC_NEW)
	FROM #FOR_UPDATE_MR Main

	UPDATE mr
		SET mr.RecordDate = TFDC.DATEREC_NEW, 
		    mr.RecordTime = TFDC.DATEREC_NEW 
	FROM AKUZ.T_MEDICAL_RECORD mr
		JOIN TMP_FOR_DELETE_CYCLE TFDC ON TFDC.ID = mr.MedicalRecordID

	-- Очищение 2й времянки для последующей вставки следующих строк
	TRUNCATE TABLE TMP_FOR_DELETE_CYCLE
	
END;

GO

DROP TABLE TMP_FOR_DELETE_CYCLE;

DROP TABLE #FOR_UPDATE_MR;

GO

if exists(select * from sys.indexes i where i.object_id = OBJECT_ID('AKUZ.T_MEDICAL_RECORD', 'U') and i.name = 'IX_T_MEDICAL_RECORD_POST')
	drop index IX_T_MEDICAL_RECORD_POST on AKUZ.T_MEDICAL_RECORD;
go
if exists(select * from sys.indexes i where i.object_id = OBJECT_ID('AKUZ.T_MEDICAL_RECORD', 'U') and i.name = 'IX_T_MEDICAL_RECORD_DATE2')
	drop index IX_T_MEDICAL_RECORD_DATE2 on AKUZ.T_MEDICAL_RECORD;
go
if exists(select * from sys.indexes i where i.object_id = OBJECT_ID('AKUZ.T_MEDICAL_RECORD', 'U') and i.name = 'IX_T_MEDICAL_RECORD_TYPE_')
	drop index IX_T_MEDICAL_RECORD_TYPE_ on AKUZ.T_MEDICAL_RECORD;
go
if exists(select * from sys.indexes i where i.object_id = OBJECT_ID('AKUZ.T_MEDICAL_RECORD', 'U') and i.name = 'IX_T_MEDICAL_RECORD_DATE')
	DROP INDEX IX_T_MEDICAL_RECORD_DATE ON AKUZ.T_MEDICAL_RECORD;
go
if exists(select * from sys.indexes i where i.object_id = OBJECT_ID('AKUZ.T_MEDICAL_RECORD', 'U') and i.name = 'IX_T_MEDICAL_RECORD_TIME')
	DROP INDEX IX_T_MEDICAL_RECORD_TIME ON AKUZ.T_MEDICAL_RECORD;
go
if exists(select * from sys.indexes i where i.object_id = OBJECT_ID('AKUZ.T_MEDICAL_RECORD', 'U') and i.name = 'IX_T_MEDICAL_RECORD_USERS')
	DROP INDEX IX_T_MEDICAL_RECORD_USERS ON AKUZ.T_MEDICAL_RECORD;
go
if exists(select * from sys.indexes i where i.object_id = OBJECT_ID('AKUZ.T_MEDICAL_RECORD', 'U') and i.name = 'IX_T_MEDICAL_RECORD_POST2')
	drop index IX_T_MEDICAL_RECORD_POST2 on AKUZ.T_MEDICAL_RECORD;
go



ALTER TABLE AKUZ.T_MEDICAL_RECORD ALTER COLUMN RecordDate datetime NOT NULL
GO
ALTER TABLE AKUZ.T_MEDICAL_RECORD ALTER COLUMN RecordTime datetime NOT NULL
GO

--CREATE INDEX IX_T_MEDICAL_RECORD_TYPE_ ON AKUZ.T_MEDICAL_RECORD ( type_ ) INCLUDE ( Post, RecordDate, RecordTime, Users) WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON)
--go
--CREATE INDEX IX_T_MEDICAL_RECORD_DATE2 ON AKUZ.T_MEDICAL_RECORD ( RecordDate ) INCLUDE ( Post, RecordTime, type_, Users) WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON)
--go
--CREATE INDEX IX_T_MEDICAL_RECORD_POST ON AKUZ.T_MEDICAL_RECORD ( Post, type_ ) INCLUDE ( RecordDate, RecordTime, Users) WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON)
--go