MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE A SELECT 'A' name UNION SELECT 'B' UNION SELECT 'C' UNION SELECT 'D';
CREATE TABLE B SELECT 'C' name, 1 manual_order_no UNION SELECT 'D', 3;
SELECT * FROM A;
SELECT * FROM B;

Records: 4  Duplicates: 0  Warnings: 0



Records: 2  Duplicates: 0  Warnings: 0




name


A

B

C

D




name
manual_order_no


C
1

D
3


WITH RECURSIVE
nums AS ( SELECT 1 manual_order_no
          UNION ALL
          SELECT manual_order_no + 1 
          FROM nums 
          WHERE manual_order_no < ( SELECT COUNT(*) 
                                    FROM A )
),
A1 AS ( 
    SELECT name, ROW_NUMBER() OVER (ORDER BY name) rn
    FROM A
    LEFT JOIN B USING (name)
    WHERE B.manual_order_no IS NULL
), 
nums1 AS (
    SELECT manual_order_no, ROW_NUMBER() OVER (ORDER BY name) rn
    FROM nums
    LEFT JOIN B USING (manual_order_no)
    WHERE B.manual_order_no IS NULL
)
SELECT name, manual_order_no
FROM B
UNION ALL
SELECT A1.name, nums1.manual_order_no
FROM A1
JOIN nums1 USING (rn)
ORDER BY manual_order_no


name
manual_order_no


C
1

A
2

D
3

B
4


INSERT INTO B (name, manual_order_no)
WITH RECURSIVE
nums AS ( SELECT 1 manual_order_no
          UNION ALL
          SELECT manual_order_no + 1 
          FROM nums 
          WHERE manual_order_no < ( SELECT COUNT(*) 
                                    FROM A )
),
A1 AS ( 
    SELECT name, ROW_NUMBER() OVER (ORDER BY name) rn
    FROM A
    LEFT JOIN B USING (name)
    WHERE B.manual_order_no IS NULL
), 
nums1 AS (
    SELECT manual_order_no, ROW_NUMBER() OVER (ORDER BY name) rn
    FROM nums
    LEFT JOIN B USING (manual_order_no)
    WHERE B.manual_order_no IS NULL
)
SELECT A1.name, nums1.manual_order_no
FROM A1
JOIN nums1 USING (rn)

Records: 2  Duplicates: 0  Warnings: 0


SELECT * FROM B


name
manual_order_no


C
1

D
3

A
2

B
4