Inserting Multiple Rows in MySQL Easily?
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.
You can use stored procedure in mysql to do the same: Inside stored procedure you can use a loop to insert multiple entries: please check below i’ve give you an example:
Procedure can be implimented like the code given below:
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!
