Sql-server – set based way to load/read a tree branch with HierarchyId

I would probably just do this the brute force way, and add indexes to support these joins where they don't exist. Not much gain to treating new customers and old customers any different once you've inserted all the customers that don't already exist:

INSERT dbo.Customer(fname, lname, address, city, state, zip, email)
    SELECT fname, lname, address, city, state, zip, email
     FROM dbo.job AS j
     WHERE job_no = @job_no
     AND NOT EXISTS
     (
        SELECT 1 FROM dbo.Customer
        WHERE fname = j.fname
        AND lname = j.lname
        AND (address = j.address OR email = j.email)
     );

INSERT INTO dbo.personal_code (customer_id, mailing_id, personal_code, email)
SELECT c.customer_id, j.mailing_id, j.personal_code, c.email
  FROM dbo.Customer AS c
  INNER JOIN dbo.job AS j
  ON c.fname = j.fname AND c.lname = j.lname 
  AND (c.address = j.address OR c.email = j.email)
  WHERE j.job_no = @job_no;

INSERT dbo.personal_code_extra(personal_code_id, extra)
SELECT pc.personal_code_id, j.extra
  FROM dbo.personal_code AS pc
  INNER JOIN dbo.Customer AS c
  ON pc.customer_id = c.customer_id
  INNER JOIN dbo.job AS j
  ON c.fname = j.fname AND c.lname = j.lname 
  AND (c.address = j.address OR c.email = j.email)
  WHERE j.job_no = @job_no;

There seems to be an MS Feedback suggestion of this kind:

https://connect.microsoft.com/SQLServer/feedback/details/3141149