MySQL - How to use IF condition to decide which columns to INSERT

Question

I am writing a stored procedure to import data from one database to another one with a completely different table structure. So I'm looking for a method to help me decide for example, if the source column is treatment_type then insert record into target's text_column (with data type varchar) but if the source column is date_enrolled then insert into target's date_column (with data type datetime). Here is a sample code.

if source column = treatment_type

INSERT INTO target_table
        (target_id, text_column)

if source column = date_enrolled

INSERT INTO target_table
        (target_id, date_column)

Basically I am iterating through this cursor below (which gives me all the columns from the source, over 100 columns at least)

DECLARE col_names CURSOR FOR SELECT column_name
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_schema = 'source_db' and table_name = 'source_table';

and I would like to create one single insert statement for each column, deciding each time the target column to insert to. Something similar to this.

FETCH col_names INTO col_name;

IF col_name = 'date_enrolled' THEN
    SET @target_column= 'date_column';
ELSE
    SET @target_column= 'text_column';
END IF;

INSERT INTO taget_table
    (target_id,  @target_column)
VALUES (
    if(col_name='tretment_type', 1, if(col_name='date_enrolled', 2, null)), 
    if(col_name='tretment_type', 'malaria', if(col_name='date_enrolled', '2020-06-03', null))
)

I will appreciate all ideas. Thank you!

Answer 1

I managed to solve this using a Prepared statement to construct the query. But first, I had to introduce a second cursor (col_types) to help me decide the column to insert to (target_column). So now the code sample that worked looks like this:

DECLARE col_names CURSOR FOR
    SELECT column_name
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_schema = 'source_db' AND table_name = 'source_table';

DECLARE col_types CURSOR FOR
    SELECT IF(column_name = 'date_enrollment', 'date_column', 'text_column') AS column_name
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_schema = 'source_db' AND table_name = 'source_table';

SELECT FOUND_ROWS() INTO num_rows;

OPEN col_names;
OPEN col_types;

SET i = 1;

the_loop: LOOP
    IF i > num_rows THEN
        CLOSE col_names;
        CLOSE col_types;
        LEAVE the_loop;
    END IF;

    FETCH col_names INTO col_name;
    FETCH col_types INTO target_column;

    -- creates target data for each source record
    SET @qrySQL = CONCAT(
        "INSERT INTO taget_table (target_id, ", target_column, ")
        VALUES (",
        IF(col_name = 'tretment_type', 1, IF(col_name = 'date_enrolled', 2, NULL)),
        IF(col_name = 'tretment_type', 'malaria', IF(col_name = 'date_enrolled', '2020-06-03', NULL)),
        ");"
    );

    PREPARE qryStmt FROM @qrySQL;
    EXECUTE qryStmt;
    DEALLOCATE PREPARE qryStmt;

    SET i = i + 1;
END LOOP the_loop;

Thanks guys!