# copy clinicians -> referrers:
INSERT INTO referrers (id, name, national_code, referral_type_id)
       SELECT id, CONCAT(surname, ', ', initials), national_code, 
		CASE WHEN national_code LIKE 'CS%' THEN 7 
			WHEN national_code LIKE 'D%' THEN 2 ELSE 1 END FROM clinicians; 
			
# copy general_practitioners -> referrers:
INSERT INTO referrers (name, national_code, referral_type_id)
       SELECT derived_name, national_code, 3 FROM general_practitioners;       

# copy clinician_organisation (clinicians) -> referrer_organisation:
INSERT INTO referrer_organisation (referrer_id, organisation_code, hospital_department_id)
    SELECT * FROM clinician_organisation;    

# copy gp ids, practice_codes & hospital_department_id -> referrer_organisation:
INSERT INTO referrer_organisation (referrer_id, organisation_code, hospital_department_id)
       SELECT r.id, g.practice_code, 600 FROM referrers r join general_practitioners g on r.national_code = g.national_code;