# 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;