Dynamic SQL : Create insert queries for tables having relations

Question

I have 2 tables, foo and bar. bar holds a foreign key to foo, such as :

foo

id | some_data
-: | :--------
 1 | a
 2 | b
 3 | c

bar

id | id_foo | more_data
-: | -----: | :--------
 1 |      1 | d
 2 |      1 | e
 3 |      2 | f
 4 |      3 | g
 5 |      3 | h
 6 |      3 | i

I would like to dynamically create queries to insert elsewhere foo and bar.

For the table foo, it's pretty easily done :

SELECT CONCAT('INSERT INTO new_foo (some_data) VALUES (''', f.some_data, ''');') AS 'sql'
FROM foo f;

I'm getting this expected result :

INSERT INTO new_foo (some_data) VALUES ('a');
INSERT INTO new_foo (some_data) VALUES ('b');
INSERT INTO new_foo (some_data) VALUES ('c');

However, I'm stuck at generating the insert queries for the table that holds the foreign key.

I thought using 2 UNIONs, one that gets the last inserted id, and the second one that uses this id to generate the queries for bar :

SELECT sql
FROM
(
    SELECT f.id AS 'id', CONCAT('INSERT INTO new_foo (some_data) VALUES (''', f.some_data, ''');') AS 'sql'
    FROM foo f
    UNION
    SELECT f.id AS 'id', 'SET @last_id = SELECT LAST_INSERT_ID();' AS 'sql' -- Target will be MySQL
    FROM foo f
    UNION
    SELECT b.id_foo AS 'id', CONCAT('INSERT INTO new_bar (id_foo, more_data) VALUES (@last_id, ''', b.more_data, ''');') AS 'sql'
    FROM bar b
) s
ORDER BY s.id;

This gives me as output :

INSERT INTO new_bar (id_foo, more_data) VALUES (@last_id, 'd');
INSERT INTO new_bar (id_foo, more_data) VALUES (@last_id, 'e');
INSERT INTO new_foo (some_data) VALUES ('a');
SET @last_id = SELECT LAST_INSERT_ID();
INSERT INTO new_bar (id_foo, more_data) VALUES (@last_id, 'f');
INSERT INTO new_foo (some_data) VALUES ('b');
SET @last_id = SELECT LAST_INSERT_ID();
INSERT INTO new_bar (id_foo, more_data) VALUES (@last_id, 'g');
INSERT INTO new_bar (id_foo, more_data) VALUES (@last_id, 'h');
INSERT INTO new_bar (id_foo, more_data) VALUES (@last_id, 'i');
INSERT INTO new_foo (some_data) VALUES ('c');
SET @last_id = SELECT LAST_INSERT_ID();

The order of the queries is wrong. It's producing first the queries for bar, then foo, then LAST_INSERT_ID().

It should be :

INSERT INTO new_foo (some_data) VALUES ('a');
SET @last_id = SELECT LAST_INSERT_ID();
INSERT INTO new_bar (id_foo, more_data) VALUES (@last_id, 'd');
INSERT INTO new_bar (id_foo, more_data) VALUES (@last_id, 'e');
INSERT INTO new_foo (some_data) VALUES ('b');
SET @last_id = SELECT LAST_INSERT_ID();
INSERT INTO new_bar (id_foo, more_data) VALUES (@last_id, 'f');
INSERT INTO new_foo (some_data) VALUES ('c');
SET @last_id = SELECT LAST_INSERT_ID();
INSERT INTO new_bar (id_foo, more_data) VALUES (@last_id, 'g');
INSERT INTO new_bar (id_foo, more_data) VALUES (@last_id, 'h');
INSERT INTO new_bar (id_foo, more_data) VALUES (@last_id, 'i');

How to ensure the correct order is respected ? I could play with the position of each query in the UNION, but I'm not sure if the orders of entries will be kept.

Here is a fiddle to play with

Answer 1

You are having three rows with the same id, and the order is not specified. You can just add a new column to specify the order:

SELECT sql
FROM
(
    SELECT f.id AS 'id', -1 AS seq, CONCAT('INSERT INTO new_foo (some_data) VALUES (''', f.some_data, ''');') AS 'sql'
    FROM foo f
    UNION
    SELECT f.id AS 'id', 0 AS seq, 'SET @last_id = SELECT LAST_INSERT_ID();' AS 'sql' -- Target will be MySQL
    FROM foo f
    UNION
    SELECT b.id_foo AS 'id', b.id AS seq, CONCAT('INSERT INTO new_bar (id_foo, more_data) VALUES (@last_id, ''', b.more_data, ''');') AS 'sql'
    FROM bar b
) s
ORDER BY s.id, s.seq;