i have modified the above query again according to wyclif's comments, and it looks like;

INSERT INTO provider(person_id, identifier, creator, date_created, retired, uuid)
SELECT u.person_id, u.system_id , 1, CURRENT_TIMESTAMP , u.retired, UUID()
FROM users as u
JOIN orders AS o ON o.orderer = u.user_id
WHERE u.person_id NOT IN (SELECT DISTINCT person_id FROM provider)

Does this achieve what we expect @wluyima  and @djazayeri