MariaDB 10.3

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE currency1 (id INT AUTO_INCREMENT, 
                        PRIMARY KEY (id), 
                        val VARCHAR(3))
SELECT 1 id, 'USD' val 
UNION ALL 
SELECT 2, 'EUR'
CREATE TABLE payment1 (id INT AUTO_INCREMENT, 
                       PRIMARY KEY (id), 
                       currency_id INT, 
                       FOREIGN KEY (currency_id) REFERENCES currency1 (id), 
                       amount INT)
SELECT 1 id, 1 currency_id, 11 amount
UNION ALL
SELECT 2 id, 2 currency_id, 22 amount
SELECT val currency_name, amount
FROM payment1
JOIN currency1 ON payment1.currency_id = currency1.id


currency_name
amount


USD
11

EUR
22


CREATE TABLE currency2 (id INT AUTO_INCREMENT, 
                        PRIMARY KEY (id), 
                        val VARCHAR(3))
SELECT 1 id, 'USD' val 
UNION ALL 
SELECT 2, 'GBP'
CREATE TABLE payment2 (id INT AUTO_INCREMENT, 
                       PRIMARY KEY (id), 
                       currency_id INT, 
                       FOREIGN KEY (currency_id) REFERENCES currency2 (id), 
                       amount INT)
SELECT 1 id, 1 currency_id, 111 amount
UNION ALL
SELECT 2 id, 2 currency_id, 222 amount
SELECT val currency_name, amount
FROM payment2
JOIN currency2 ON payment2.currency_id = currency2.id


currency_name
amount


USD
111

GBP
222


-- Prepare currency1 table for inserting data
ALTER TABLE currency1 
ADD UNIQUE INDEX currency_name (val)
-- Insert currencies
INSERT IGNORE INTO currency1 (val)
SELECT val FROM currency2
SELECT * FROM currency1


id
val


2
EUR

3
GBP

1
USD


-- Prepare payment1 table for inserting data
ALTER TABLE payment1 
ADD COLUMN source INT DEFAULT 1
-- Insert payments
INSERT INTO payment1 (currency_id, amount, source)
SELECT currency1.id, payment2.amount, 2
FROM payment2
JOIN currency2 ON payment2.currency_id = currency2.id
JOIN currency1 ON currency2.val = currency1.val
-- Check the result
SELECT val currency_name, amount, source
FROM payment1
JOIN currency1 ON payment1.currency_id = currency1.id


currency_name
amount
source


USD
11
1

EUR
22
1

USD
111
2

GBP
222
2