title: MySQL stored procedure insert multiple rows from list
body: <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>