Plan A - Import in batches
DECLARE @intMinPatientID INT = 0, @intMaxPatientID INT = 0, @intRowCount = 1 -- 1=Force first iteration
, @intBatchSize int = 100000 -- Iteration batch size (set to a size that works OK!)
--
WHILE @intRowCount > 0
BEGIN
--
SELECT TOP @intBatchSize
@intMaxPatientID = PatientID
FROM dbo.EmisEventExtraction
WHERE PatientID > @intMinPatientID -- See note #2
ORDER BY PatientID
SELECT @intRowCount = @@ROWCOUNT -- Number of rows found
--
INSERT INTO dbo.EmisEvent (EventGuidDigest,PatientID,ConsultationID,StaffID,EventDate,EventType,MedicalCodeID,Value,Unit,Abnormal)
SELECT EventGuidDigest,PatientID,ConsultationID,StaffID,EventDate,EventType,MedicalCodeID,Value,Unit,Abnormal
FROM dbo.EmisEventExtraction WITH (tablock)
--
WHERE PatientID > @intMinPatientID AND PatientID <= @intMaxPatientID
ORDER BY PatientID -- See note #1
--
SELECT @intMinPatientID = @intMaxPatientID
--
CHECKPOINT
--
END
1 It would be best that the inserts are in Clustered Index Order
2 Choose a column with high selectivity. If it is NOT unique some batches will be larger than others. If it is NOT the Clustered Index key then insert order will not match clustered index, and that may create a lot of work maintaining the clustered index as inserts are made.
Plan B:
Export the whole VIEW to an external file, using BCP (Native mode, not CSV), and using a query pre-sorted into the clustered index order (of the TARGET table, NOT the Source table)
Import the file into the new table, and provide the HINT to indicate that the file is already ordered on Clustered Index
I expect that Plan B will be faster ...