insert multiple rows into table from a variable in mysql 5.7

Question

Here is my issue. I have the following stored procedure in which i pass the variable 'session_ids' which is actually a set of values ('1','2','3',..., 'n'). How can i pass-split each of these values into corresponding number of rows to a temporary table? For example, row 1 has the value '1', row 2 has the value '2',..., row n has the value 'n'.

Actually, i want to do this dynamically because i don't know the number of values in the variable 'session_ids'. I have this in my mind but it doesn't work.

PROCEDURE `migrate_session_ids`(IN session_ids LONGTEXT)
CREATE TEMPORARY TABLE tempTable (id INT NOT NULL AUTO_INCREMENT, session_id BIGINT, PRIMARY KEY (`id`)); 
INSERT INTO tempTable(session_id) SELECT * FROM session_ids;

Thank you in advance

GMB · Accepted Answer · 2020-05-12 23:46:07Z

2

If you are running MySQL 8.0, one option is to use a recursive query to split the delimited string:

delimiter //
create procedure migrate_session_ids(in session_ids longtext)
begin

    create temporary table temptable (
        id int not null auto_increment, 
        session_id bigint, 
        primary key (id)
    );

    insert into temptable(session_id)
    with recursive all_ids as (
        select 
            0 + substring(concat(session_ids, ','), 1, locate(',', concat(session_ids, ',')) -1) id,
            substring(concat(session_ids, ','), locate(',', concat(session_ids, ',')) + 1) rest
        union all
        select 
            0 + substring(rest, 1, locate(',', rest) - 1),
            substring(rest, locate(',', rest) + 1) 
        from all_ids
        where locate(',', rest) > 0
    )
    select id from all_ids;

end//

Demo on DB Fiddle:

call migrate_session_ids('1,4,7');
select * from temptable;

| id  | session_id |
| --- | ---------- |
| 1   | 1          |
| 2   | 4          |
| 3   | 7          |

answered May 12, 2020 at 23:46

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Karistinakis Over a year ago

Thanks for the quick response. I'm running MySQL 5.7 and using mysql workbench 8.0. I got this error: << Schema Error: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'recursive all_ids as ( select 0 + substring(concat(session_ids, ','), 1, l' at line 9 >>

John Karistinakis · Accepted Answer · 2020-05-14 10:51:42Z

0

It didn't work the recuring query in my case, because I run MySQL 5.7 and I am confused with the solutions (conversions of this) on the web for this MySQL version. So, finally I came up with a solution using 'concat' and dynamic query like this, and wanted to share it in case someone faces similar issue:

SET @sql = concat('INSERT INTO tempTable(session_id) SELECT id_session FROM Notes WHERE id_session IN (',session_ids,')');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

answered May 14, 2020 at 10:51

John Karistinakis

1211 silver badge12 bronze badges

Collectives™ on Stack Overflow

insert multiple rows into table from a variable in mysql 5.7

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related