Inserting Multiple Rows in MySQL Easily?

Question

I'm having a bit of trouble. I need to award an item to users on our site, but I don't want to manually fill in the numbers one by one. Is there a way to set the SQL query to INSERT INTO from UID 9 to 5430 without having to create multiple lines? Here's my example.

INSERT INTO `item_owned` (`id`, `uid`, `iid`, `kind`, `time_owned`, `notes`) VALUES (NULL, 'x', '3626', '1', '1592596732', 'NotBanned')

I'm trying to have the "x" be a number, but to have MYSQL generate multiple numbers from 9 to 5430 without having to generate multiple numbers/code all at once. So something like:

INSERT INTO `item_owned` (`id`, `uid`, `iid`, `kind`, `time_owned`, `notes`) VALUES (NULL, '9 - 5430', '3626', '1', '1592596732', 'NotBanned')

The 9 - 5430 is where the issue is. I want to award the item to everyone who has their number between the number 9 and 5430.

Help appreciated - thanks.

Does this answer your question? [MySQL How do you INSERT INTO a table with a SELECT subquery returning multiple rows?](https://stackoverflow.com/questions/9422529/mysql-how-do-you-insert-into-a-table-with-a-select-subquery-returning-multiple-r) — kmoser, Jul 12 '20 at 06:15
Make it between 9 and 15 ,say, and see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Jul 12 '20 at 08:38

score 0 · Answer 1 · edited Aug 08 '20 at 16:35

You can use stored procedure in mysql to do this; inside your stored procedure you can use a loop to insert multiple entries. I've provided an example below.

Procedure can be implemented like the code shown here:

delimiter $$
create procedure fill_rows(in start_index int,in termination_point int)
begin
    while start_index <= termination_point do
        INSERT INTO `item_owned` (`id`, `uid`, `iid`, `kind`, `time_owned`, `notes`) VALUES (NULL, start_index, '3626', '1', '1592596732', 'NotBanned');
        set start_index := start_index + 1;
    end while;
end $$
delimiter ;

Now whenever you want to insert uid let's say from range x to y. Assume x = 10 and y = 1000 then you can simply insert this records using this one time procedure call like:

call fill_row(10, 1000);

This call will insert 990 new rows with uid values 10, 11, 12 ...1000. Hope this may help you!

If you have any further doubt you can drop a comment here or if this answer helps you to solve the problem then please mark it as an answer. Thank you! — abhijeet chavan, Jul 13 '20 at 03:11

Inserting Multiple Rows in MySQL Easily?

1 Answers1