how to insert multiple comma separated values into multiple columns

Question

i need a stored procedure to insert comma separated values to be inserted like below

contact_id = '1000, 1002, 1003, 1004';

first_name = 'dirt,dammy , samii ,samii ,xenon ';

using a stored procedure

...

i tried like

BEGIN


 drop temporary table if exists t_srcids;
        create temporary table t_srcids(srcid char(255), names char(255));
        set @vIds = testSourceId;
        set @vNames = testSourceName;

       

       
       
        set @value = replace(@vNames,",","'),(");

        set @sql = concat("insert into t_srcids (srcid) values ('", replace(@vIds,",", "'),('"),"');");
        prepare stmt1 from @sql;
        execute stmt1;

        set @sql1 = concat("insert into t_srcids (names) values ('", replace(@vNames,",", "'),('"),"');");
        prepare stmt2 from @sql1;
        execute stmt2;

        select * from t_srcids;


END;

which produces single insert need a help another columns as well

Answer 1

drop temporary table if exists t_srcids;
        create temporary table t_srcids(Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,srcid char(255));
        set @vIds = testSourceId;
        set @vNames = testSourceName;

        set @sql = concat("insert into t_srcids (srcid) values ('", replace(@vIds,",", "'),('"),"');");
        prepare stmt1 from @sql;
        execute stmt1;

        drop temporary table if exists temp_names;
        create temporary table temp_names(Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, names char(255));

        set @vNames = testSourceName;

        set @sql2 = concat("insert into temp_names (names) values ('", replace(@vNames,",", "'),('"),"');");
        prepare stmt2 from @sql2;
        execute stmt2;

        select tId.srcid,tNames.names from t_srcids tId join temp_names tNames on  tId.Id = tNames.Id;

instead of trying to insert in same table at same time i used different table and select them with join as above.

this can be done with loop or while but that will be not an optimized solution. so i recommend this solution for comma separated values passed as parameter to avoid loops, thanks