MySQL stored procedure insert multiple rows from list
<p>How can I write a MySQL stored procedure to insert values from a variable sized list? More specifically I need to insert data into one parent table, get the ID from the insert, and then insert a variable number of child records along with the new ID into another table in a one-to-many relationship. My schema looks something like this:</p>

<p>TableA:</p>

<pre><code>table_a_id -- Auto Increment
counter
some_data...
</code></pre>

<p>TableB:</p>

<pre><code>table_b_id -- Auto Increment
table_a_id -- Foreign Key Constraint
some_data_from_list...
</code></pre>

<p>My stored procedure so far looks like this:</p>

<pre class="lang-sql prettyprint-override"><code>DELIMITER ;;
CREATE PROCEDURE insert_group_alert(
  IN _some_data_a         VARCHAR(255),
  IN _data_list_b         TEXT,
)
  BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
      ROLLBACK;
    END;
    START TRANSACTION;

    INSERT INTO TableA (
      some_data,
      counter
    )
    VALUES (
      _some_data_a,
      1
    )
    ON DUPLICATE KEY UPDATE
      counter          = counter + 1;

    SELECT last_insert_id()
      INTO @newId;

LIST INSERT ???:
    INSERT INTO TableB (
      table_a_id, some_data
    ) VALUES (
      @newId,
      list_item,
    );
END LIST INSERT ???

    COMMIT;
  END ;;
DELIMITER ;
</code></pre>

<p>My thought was to pass in a list of items to insert into table B via a comma delimited string. The values are strings. I am not sure what to do in the LIST INSERT section. Do I need a loop of some sort? Is this stored procedure I have so far the correct way to do this? I don't want to do a batch as I could potentially have hundreds or even thousands of items in the list. Is there a better solution? I am using straight JDBC.</p>