Pass list of values to be inserted as parameter to stored procedure

I have this stored procedure in MySQL version 5.7. If I want to create n elements it would be much faster to insert n elements in one statement two times rather than call the stored procedure n times.

How can I create a stored procedure that takes a “list” of names, a team id and does this?

CREATE PROCEDURE create_data_user(IN name VARCHAR(100), IN data_user_team_id INT)
BEGIN
START TRANSACTION;
INSERT INTO users (users.name, users.type)
VALUES (name, "team_data");
INSERT INTO team_members (team_id, user_id, mod_time)
VALUES (data_user_team_id, LAST_INSERT_ID(), UNIX_TIMESTAMP());
COMMIT;
END ;;

A stored procedure that creates two elements would look like this:

CREATE PROCEDURE create_data_user(IN name VARCHAR(100), IN name2 VARCHAR(100), IN 
data_user_team_id INT)
BEGIN
START TRANSACTION;
INSERT INTO users (users.name, users.type)
VALUES 
(name, "team_data"),
(name2, "team_data");
INSERT INTO team_members (team_id, user_id, mod_time)
VALUES 
(data_user_team_id, LAST_INSERT_ID(), UNIX_TIMESTAMP()),
(data_user_team_id, LAST_INSERT_ID()+1, UNIX_TIMESTAMP());
COMMIT;
END ;;

Edit:
There are different ways to tackle this. You could just scrap the SP and copy-paste batch insert code. Rick James presented a complex solution, but I think you can do something simpler, perhaps at some cost in performance (but better than many individual inserts).

On the caller side you do something like this:

CREATE TEMPORARY TABLE names (name);

INSERT INTO names
VALUES
(name1),
(name2),
...;

CALL create_data_users(1);

And then the stored procedure is like this:

CREATE PROCEDURE create_data_users(IN data_user_team_id INT)
BEGIN
START TRANSACTION;
    INSERT INTO users (users.name, users.type) 
    SELECT name, "team_data" FROM names; 
    SET @num=LAST_INSERT_ID(); 
    INSERT INTO team_members (team_id, user_id, mod_time) 
    SELECT data_user_team_id, @num:<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="bc81fcd2c9d1">[email protected]</a>+1, UNIX_TIMESTAMP() FROM names;
COMMIT;
END ;;

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

The best efficient generalization, especially for long lists, I have found is to

1. Build a temp table
2. Use a batch INSERT to populate the table
3. INSERT ... SELECT ... to get the items in the real table
4. Do a complex update with a join to pull back all the ids.

(No loops involved.)

Specifics: http://mysql.rjweb.org/doc.php/staging_table#normalization

(That is focused around the very likely situation where the names are already in the table. However, it should work fine for your case.)

Method 2

CREATE PROCEDURE create_data_users (IN users_list TEXT, IN data_user_team_id INT)
BEGIN
    DECLARE name VARCHAR(255);
    REPEAT
        SET name = TRIM(SUBSTRING_INDEX(users_list, ',', 1));
        SET users_list = CASE WHEN LOCATE(',', users_list)
                              THEN TRIM(SUBSTRING(users_list FROM 1 + LOCATE(',', users_list)))
                              ELSE '' END;
        INSERT INTO users (users.name, users.type)
        VALUES (name, "team_data");
        INSERT INTO team_members (team_id, user_id, mod_time)
        VALUES (data_user_team_id, LAST_INSERT_ID(), UNIX_TIMESTAMP());
    UNTIL users_list = '' END REPEAT;
END

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=37e397c1066e1e8459df70fc6131e5d4

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0