# begin transaction

delimiter //
BEGIN NOT ATOMIC

SET @v_level=0;

DROP TABLE IF EXISTS t_copy;

CREATE TEMPORARY TABLE t_copy
SELECT f_node_id, f_parent_id from t_tm1_export_import_tree
LIMIT 0;

ALTER TABLE t_copy ADD f_cloned_id INT(10) UNSIGNED;
ALTER TABLE t_copy ADD f_level     INT(10) UNSIGNED ;
ALTER TABLE t_copy ADD INDEX(f_level);

ALTER TABLE t_copy MODIFY COLUMN f_node_id INT auto_increment PRIMARY KEY;


# --- INIT tree roots
INSERT
    INTO t_copy
    (f_cloned_id, f_parent_id, f_level )
    SELECT  f_node_id, NULL, @v_level
    FROM t_tm1_export_import_tree
    WHERE f_node_id = 22
;

# --- MariaDB doesn't allow self joins with tmp tables :-/
#     i.e need a second temp table

DROP TABLE IF EXISTS parents;

CREATE TEMPORARY TABLE parents
    SELECT * from t_copy
    WHERE f_level = @v_level;


# --- recursive Copy

WHILE ( row_count() ) DO

    SET @v_level = @v_level+1;

    INSERT INTO t_copy
                (f_parent_id,   f_cloned_id,    f_level)
        SELECT  P.f_node_id,    T.f_node_id,    @v_level
        FROM t_tm1_export_import_tree as  T
        JOIN PARENTS AS P
        ON   T.f_parent_id = P.f_cloned_id
    ;

    TRUNCATE TABLE parents;

    INSERT INTO parents
        SELECT * from t_copy
        WHERE f_level = @v_level
    ;

END WHILE;


# ====== result


# --- add to max ID of target table

SELECT max(f_node_id) into @target_id from t_tm1_export_import_tree;

IF (FALSE) THEN

    INSERT INTO t_tm1_export_import_tree
        SELECT  t_copy.f_node_id    + @target_id, 
                t_copy.f_parent_id  + @target_id, 
                T.f_bezeichner, 
                T.f_sort, 
                T.f_insert, "Y" AS f_duplicate
        FROM    t_copy
        JOIN    t_tm1_export_import_tree AS T
        ON      t_copy.f_cloned_id = T.f_node_id;
END IF;


END//

# --- dump t_copy for testing
IF (TRUE) THEN
    SELECT *
    FROM t_copy
    JOIN t_tm1_export_import_tree AS T
    ON t_copy.f_cloned_id = T.f_node_id;
END IF;