SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE USERS_OLD (id INTEGER, email VARCHAR(50));

INSERT INTO USERS_OLD VALUES
  (1, 'test1@email.com'),
  (2, 'test2@email.com'),
  (3, 'test3@email.com'),
  (4, 'test4@email.com');
  
  
CREATE TABLE USER_ORDERS_OLD (user_id INTEGER, order_id VARCHAR(50));

INSERT INTO USER_ORDERS_OLD VALUES
  (1, 'DLEFGM'),
  (2, 'OPDFGT'),
  (3, 'UZDFGP'),
  (4, 'POIDSX');
  

CREATE TABLE USERS_NEW (id INTEGER, email VARCHAR(50));

INSERT INTO USERS_NEW VALUES
  (5, 'test1@email.com'),
  (9, 'test2@email.com'),
  (10, 'test3@email.com'),
  (17, 'test4@email.com');
  
12 rows affected

SELECT u3.id, u2.order_id
FROM USERS_OLD u1
JOIN USER_ORDERS_OLD u2 ON u1.id = u2.user_id
JOIN USERS_NEW u3 ON u1.email = u3.email


id
order_id


5
DLEFGM

9
OPDFGT

10
UZDFGP

17
POIDSX


CREATE TABLE USER_ORDERS_NEW (user_id INTEGER, order_id VARCHAR(50));

INSERT INTO USER_ORDERS_NEW (user_id, order_id)
       SELECT u3.id, u2.order_id
       FROM USERS_OLD u1
       JOIN USER_ORDERS_OLD u2 ON u1.id = u2.user_id
       JOIN USERS_NEW u3 ON u1.email = u3.email;
4 rows affected

SELECT * FROM USER_ORDERS_NEW;


user_id
order_id


5
DLEFGM

9
OPDFGT

10
UZDFGP

17
POIDSX