Is it possible to refactor this MySQL stored procedure loop?

Question

Hi! I have a loop in MySQL stored procedure where i insert record almost for each iteration. Well known problem is that inserting row-by-row is inefficient and i'd rather see inserts with multiple value lists instead.

Pseudo-example of current procedure:

CREATE PROCEDURE p_foo_bar()
BEGIN
  DECLARE foo VARCHAR(255);
  DECLARE my_cursor CURSOR FOR SELECT foo FROM bar;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found = 1;

  cursor_loop:
  LOOP
    FETCH my_cursor INTO foo;

    IF not_found THEN
      CLOSE my_cursor;
      LEAVE
    END IF;

    IF foo is something ... THEN
      INSERT INTO foobar (foo_colum) VALUES (foo);

      -- Anyone knows if it is possible to do bulk insert here:
      -- For example:
      -- INSERT INTO foobar (foo_column) VALUES (foo), ..., (foo123);
    END IF;
  END LOOP cursor_loop;
END

I'd rather see the INSERT query to grow it's values list for some time and then periodically making inserts in bulks but cannot see the way if it is possible without cooking a huge bowl of spaghetti.

Answer 1

try this:

 INSERT INTO foobar (foo_column, , ....)
    SELECT foo, ..., foo123
    FROM bar
    WHERE foo is something ...

Answer 2

Are you certain you need to iterate over "SELECT foo FROM bar" in that way ...

Is there no way to do

insert into foobar (foo_column) 
select foo 
  from bar 
  where foo is something

??

Answer 3

how about building a prepared statement and executing it after the loop

http://dev.mysql.com/tech-resources/articles/4.1/prepared-statements.html

declare @column_names text;
declare @column_values text;

Then inside loop add your column name and values to the variables

When the loop has finished you ccan build your query and execute

set @sql_text = CONCAT('INSERT INTO foobar (', @column_names, 
                 ') VALUES (', @column_values, ')';

prepare stmt from @sql_text;

execute stmt;

Josh

Answer 4

This isn't just a MySQL issue, it occurs in any decently large RDBMS (of course, Access excluded). What you describe is called 'Set logic'. What is the 'if' and loop doing that a good where clause doesn't? Does the source information change over time and the loop is essentially polling? If that is true, you'll still need some kind of loop but you are better off using a where clause to get as many rows inserted at once as you can and then create a loop that lets you up-date over time. That type of loop is probably better off being outside of your stored proc. Another answer has already given the general SQL that I would have suggested.