CODEWITHSUNDEEP
mysqlsqlstored-proceduressql-insertrecursive-query
John Karistinakis
John Karistinakis

Reputation: 121

insert multiple rows into table from a variable in mysql 5.7

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

Upvotes: 1

Views: 1333

Answers (2)

John Karistinakis
John Karistinakis

Reputation: 121

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;

Upvotes: 0

GMB
GMB

Reputation: 222442

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          |

Upvotes: 2

Related Questions