MySQL: repeated insertion into a table

Question

Given a book number id and the number of book copies, I want to insert as many rows into a table as there are book copies. This is my non-working solution:

DROP PROCEDURE IF EXISTS insert_into_book_copy_table;
CREATE PROCEDURE insert_into_book_copy_table (IN in_book_id INT,
                                              IN in_num     INT)
BEGIN

    SET @I = 1;
    SET @ACQUIRED = CURRENT_TIMESTAMP;

    WHILE I <= in_num DO 
        INSERT INTO book_copy (book_id, acquired) # The table has an
        VALUES (in_book_id, @ACQUIRED);           # auto-increment field
                                                  # which I didn't list here
        SET @I = @I + 1;
    END WHILE;

END$$

For instance, if the given book number id is 23 and the number of book copies is equal to 8 then the book_copy table should look like this:

SELECT * FROM book_copy;
╔══════════════╦══════════╦═════════════════════╗
║ book_copy_id ║  book_id ║      acquired       ║
╠══════════════╬══════════╬═════════════════════╣
║            1 ║       23 ║ 2013-04-15 18:15:20 ║
║            2 ║       23 ║ 2013-04-15 18:15:20 ║
║            3 ║       23 ║ 2013-04-15 18:15:20 ║
║            4 ║       23 ║ 2013-04-15 18:15:20 ║
║            5 ║       23 ║ 2013-04-15 18:15:20 ║
║            6 ║       23 ║ 2013-04-15 18:15:20 ║
║            7 ║       23 ║ 2013-04-15 18:15:20 ║
║            8 ║       23 ║ 2013-04-15 18:15:20 ║
╚══════════════╩══════════╩═════════════════════╝

And is there a better way of coding this?

Oh yeah, that's it. I overlooked the I. But do you think this is a good way of coding it? — Mikhail
– Mikhail, Commented Apr 15, 2013 at 14:44

Kickstart · Accepted Answer · 2013-04-15 15:34:46Z

Try this, as a single SQL statement:-

INSERT INTO book_copy (book_id, acquired)
SELECT a.i+b.i*10+c.i*100 + 1, $BookId, NOW()
FROM integers a, integers b, integers c
WHERE a.i+b.i*10+c.i*100 < $NumberToInsert

Relies on a table of integers with a single column (called i) with 10 rows, with the values 0 to 9. Joining this against itself twice gives all the numbers from 0 to 999 (can easily be expanded), hence can insert up to 1000 books in one statement.

EDIT - if you don't want to create a table of integers then you can do the same with a couple of subqueries returning constants:-

INSERT INTO book_copy (book_id, acquired)
SELECT a.i+b.i*10+c.i*100 + 1, $BookId, NOW()
FROM (SELECT 0 AS i UNION SELECT 1 AS i UNION SELECT 2 AS i UNION SELECT 3 AS i UNION SELECT 4 AS i UNION SELECT 5 AS i UNION SELECT 6 AS i UNION SELECT 7 AS i UNION SELECT 8 AS i UNION SELECT 9 AS i ) a,
(SELECT 0 AS i UNION SELECT 1 AS i UNION SELECT 2 AS i UNION SELECT 3 AS i UNION SELECT 4 AS i UNION SELECT 5 AS i UNION SELECT 6 AS i UNION SELECT 7 AS i UNION SELECT 8 AS i UNION SELECT 9 AS i ) b,
(SELECT 0 AS i UNION SELECT 1 AS i UNION SELECT 2 AS i UNION SELECT 3 AS i UNION SELECT 4 AS i UNION SELECT 5 AS i UNION SELECT 6 AS i UNION SELECT 7 AS i UNION SELECT 8 AS i UNION SELECT 9 AS i ) c
WHERE a.i+b.i*10+c.i*100 < $NumberToInsert

Collectives™ on Stack Overflow

MySQL: repeated insertion into a table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related