I have the following insert statement that i am running daily and updating approx 55,000 records.
INSERT INTO dbo.ETL_INPUT (
SCH_APPT_ID
,SCH_EVENT_ID
,MRN
,PERSON_ID
,NAME_FULL_FORMATTED
,Main_Bookshelf_Old
,Main_Bookshelf_New
,Old_Book_Shelf
,First_Book_Old
,First_Book_New
,Old_Book
,Second_Book_Old
,Second_Book_New
,Third_Book_Old
,Third_Book_New
,Final_Book_Old
,Final_Book_New
,Old_Clinic
,Old_Rsrc
,Rsrc_Old
,Rsrc_New
,Old_Appt_Type
,Appt_Type_Old
,Appt_Type_New
,Amb_Loc
,Amb_Loc_Old
,Amb_Loc_New
,SLOT_TYPE_ID
,BEG_DT_TM
,END_DT_TM
,DURATION
,SCH_COMMENT
,Appt_State
,XRay_Requested
,Referring_Physician
,PRIORITY
,TRANSPORT_MODE
,REASON_FOR_EXAM
,Reason_For_Exam_DCP
,Ambulance_Required
,Interpreter
,QH_LANGUAGE_SPOKEN
,Private_Patient
,RADIOLOGY_DIABETIC
,PREVIOUS_REACTION_TO_CONTRAST
,Urgency_Category
,New_Review
,Trial_Pt_information
,CONSULTING_PHYSICIAN
,QH_REFERRAL_DATE_RECEIVED
,Date_Planned_for_Operating_Theatre
,QH_REFERRAL_DATE_CATEGORISED
,QH_REFERRAL_DATE
,RADIOLOGY_ASTHMATIC
,SCHEDULING_ORDER_TYPE
,RADIOLOGY_SCHEDULING_ORDERING_PHYSICIAN
,RADIOLOGY_INPATIENT_OUTPATIENT
,CONFIDENTIALITY_LEVEL
,RADIOLOGY_CONTRAST_3CS
,RADIOLOGY_MRI_IS_THIS_PATIENT_IN_THE_0715_TIME_SLOT
,Multidisiplinary_Care_Plan_Clinic
,Admittable_Procedure
,QH_ISIS_REQUEST_NUMBER
,QH_APPOINTMENT_REFERRAL_SOURCE
,QH_Not_Ready_For_Care_Start_Date
,QH_Not_Ready_For_Care_End_Date
,QH_REFERRAL_CATEGORY_CODE
,QH_LOCATION_REGION_CODE
,QH_LOCATION_ZONE_CODE
,QH_LOCATION_STATE_CODES
,QH_FACILITY_CODES
,QH_INTENDED_FINANCIAL_PAYMENT_CLASS
,QH_SERVICE_DELIVERY_MODE
,QH_DELIVERY_SETTING
,RADIOLOGY_REQUIRED_BY_DATE
,RADIOLOGY_BMD_SCAN_DATE_PRIOR_SCAN
,OP_Sub_Specialty_Code
,OP_Category_Code
,OP_Session_Type
,OP_Service_Delivery_Code
,OP_Clinic_Type
,OP_Delivery_Setting
,OP_Care_Type
,OP_Service_Type
,OP_Referral_Source
,OP_Provisional_Diagnosis
,OP_Ready_For_Care
,OP_Last_Clinical_Update_Date
,OP_Referral_Duration
,OP_Corporate_Clinic_Code
,QH_APPOINTMENT_STANDBY_CODE
,RADIOLOGY_AFTER_HOURS_ARRIVAL_VARIATION_RECEPTION_DESK
,Clinical_Area
)
SELECT SCH_APPT_ID
,SCH_EVENT_ID
,MRN
,PERSON_ID
,NAME_FULL_FORMATTED
,Main_Bookshelf_Old
,Main_Bookshelf_New
,Old_Book_Shelf
,First_Book_Old
,First_Book_New
,Old_Book
,Second_Book_Old
,Second_Book_New
,Third_Book_Old
,Third_Book_New
,Final_Book_Old
,Final_Book_New
,Old_Clinic
,Old_Rsrc
,Rsrc_Old
,Rsrc_New
,Old_Appt_Type
,Appt_Type_Old
,Appt_Type_New
,Old_Amb_Loc
,Amb_Loc_Old
,Amb_Loc_New
,SLOT_TYPE_ID
,BEG_DT_TM
,END_DT_TM
,DURATION
,SCH_COMMENT
,Appt_State
,XRay_Requested
,Referring_Physician
,PRIORITY
,TRANSPORT_MODE
,REASON_FOR_EXAM
,Reason_For_Exam_DCP
,Ambulance_Required
,Interpreter
,QH_LANGUAGE_SPOKEN
,Private_Patient
,RADIOLOGY_DIABETIC
,PREVIOUS_REACTION_TO_CONTRAST
,Urgency_Category
,New_Review
,Trial_Pt_information
,CONSULTING_PHYSICIAN
,QH_REFERRAL_DATE_RECEIVED
,Date_Planned_for_Operating_Theatre
,QH_REFERRAL_DATE_CATEGORISED
,QH_REFERRAL_DATE
,RADIOLOGY_ASTHMATIC
,SCHEDULING_ORDER_TYPE
,RADIOLOGY_SCHEDULING_ORDERING_PHYSICIAN
,RADIOLOGY_INPATIENT_OUTPATIENT
,CONFIDENTIALITY_LEVEL
,RADIOLOGY_CONTRAST_3CS
,RADIOLOGY_MRI_IS_THIS_PATIENT_IN_THE_0715_TIME_SLOT
,Multidisiplinary_Care_Plan_Clinic
,Admittable_Procedure
,QH_ISIS_REQUEST_NUMBER
,QH_APPOINTMENT_REFERRAL_SOURCE
,QH_Not_Ready_For_Care_Start_Date
,QH_Not_Ready_For_Care_End_Date
,QH_REFERRAL_CATEGORY_CODE
,QH_LOCATION_REGION_CODE
,QH_LOCATION_ZONE_CODE
,QH_LOCATION_STATE_CODES
,QH_FACILITY_CODES
,QH_INTENDED_FINANCIAL_PAYMENT_CLASS
,QH_SERVICE_DELIVERY_MODE
,QH_DELIVERY_SETTING
,RADIOLOGY_REQUIRED_BY_DATE
,RADIOLOGY_BMD_SCAN_DATE_PRIOR_SCAN
,OP_Sub_Specialty_Code
,OP_Category_Code
,OP_Session_Type
,OP_Service_Delivery_Code
,OP_Clinic_Type
,OP_Delivery_Setting
,OP_Care_Type
,OP_Service_Type
,OP_Referral_Source
,OP_Provisional_Diagnosis
,OP_Ready_For_Care
,OP_Last_Clinical_Update_Date
,OP_Referral_Duration
,OP_Corporate_Clinic_Code
,QH_APPOINTMENT_STANDBY_CODE
,RADIOLOGY_AFTER_HOURS_ARRIVAL_VARIATION_RECEPTION_DESK
,Clinical_Area
FROM dbo.vw_ETL_INPUT
EXCEPT
SELECT SCH_APPT_ID
,SCH_EVENT_ID
,MRN
,PERSON_ID
,NAME_FULL_FORMATTED
,Main_Bookshelf_Old
,Main_Bookshelf_New
,Old_Book_Shelf
,First_Book_Old
,First_Book_New
,Old_Book
,Second_Book_Old
,Second_Book_New
,Third_Book_Old
,Third_Book_New
,Final_Book_Old
,Final_Book_New
,Old_Clinic
,Old_Rsrc
,Rsrc_Old
,Rsrc_New
,Old_Appt_Type
,Appt_Type_Old
,Appt_Type_New
,Amb_Loc
,Amb_Loc_Old
,Amb_Loc_New
,SLOT_TYPE_ID
,BEG_DT_TM
,END_DT_TM
,DURATION
,SCH_COMMENT
,Appt_State
,XRay_Requested
,Referring_Physician
,PRIORITY
,TRANSPORT_MODE
,REASON_FOR_EXAM
,Reason_For_Exam_DCP
,Ambulance_Required
,Interpreter
,QH_LANGUAGE_SPOKEN
,Private_Patient
,RADIOLOGY_DIABETIC
,PREVIOUS_REACTION_TO_CONTRAST
,Urgency_Category
,New_Review
,Trial_Pt_information
,CONSULTING_PHYSICIAN
,QH_REFERRAL_DATE_RECEIVED
,Date_Planned_for_Operating_Theatre
,QH_REFERRAL_DATE_CATEGORISED
,QH_REFERRAL_DATE
,RADIOLOGY_ASTHMATIC
,SCHEDULING_ORDER_TYPE
,RADIOLOGY_SCHEDULING_ORDERING_PHYSICIAN
,RADIOLOGY_INPATIENT_OUTPATIENT
,CONFIDENTIALITY_LEVEL
,RADIOLOGY_CONTRAST_3CS
,RADIOLOGY_MRI_IS_THIS_PATIENT_IN_THE_0715_TIME_SLOT
,Multidisiplinary_Care_Plan_Clinic
,Admittable_Procedure
,QH_ISIS_REQUEST_NUMBER
,QH_APPOINTMENT_REFERRAL_SOURCE
,QH_Not_Ready_For_Care_Start_Date
,QH_Not_Ready_For_Care_End_Date
,QH_REFERRAL_CATEGORY_CODE
,QH_LOCATION_REGION_CODE
,QH_LOCATION_ZONE_CODE
,QH_LOCATION_STATE_CODES
,QH_FACILITY_CODES
,QH_INTENDED_FINANCIAL_PAYMENT_CLASS
,QH_SERVICE_DELIVERY_MODE
,QH_DELIVERY_SETTING
,RADIOLOGY_REQUIRED_BY_DATE
,RADIOLOGY_BMD_SCAN_DATE_PRIOR_SCAN
,OP_Sub_Specialty_Code
,OP_Category_Code
,OP_Session_Type
,OP_Service_Delivery_Code
,OP_Clinic_Type
,OP_Delivery_Setting
,OP_Care_Type
,OP_Service_Type
,OP_Referral_Source
,OP_Provisional_Diagnosis
,OP_Ready_For_Care
,OP_Last_Clinical_Update_Date
,OP_Referral_Duration
,OP_Corporate_Clinic_Code
,QH_APPOINTMENT_STANDBY_CODE
,RADIOLOGY_AFTER_HOURS_ARRIVAL_VARIATION_RECEPTION_DESK
,Clinical_Area
FROM dbo.ETL_INPUT`
I am wondering if there is a way to optimize this by incrementally commiting records rather than trying to do all at once?
