I need to emulate some test data and so I need to be able insert a random number of rows, let's say from 1 to 100 rows, into MySQL table with the following schema:
QRID Login
create table test_data
(
id int
,create_date datetime
)
id column must be unique b/c it is a primary key. create_date should contain the datetime when the row has been inserted. Please note that table is NOT EMPTY, i.e. it already contains a whole bunch of existing data.
I want to do it with entirely in SQL stored routine that takes no input parameters and just inserts a random number of rows between 1 and 100 into the table above (this is not the exact table structure, I've simplified it to make things a bit easier)
Any idea on how to do it in SQL, without without using PHP code?
Thanks!
Try this steps:
Create table:
drop table if exists test_data;
create table test_data
(id int unsigned not null auto_increment primary key,
create_date datetime)
engine=innodb;
Create procedure:
drop procedure if exists insert_test_data;
delimiter #
create procedure insert_test_data()
begin
declare v_max int unsigned default floor(1 + (rand() * 100));
declare v_counter int unsigned default 0;
-- truncate table test_data; <- commenting this line out, b/c we need to preserve existing data - based on the requirements above
start transaction;
while v_counter < v_max do
insert into test_data (create_date) values now();
set v_counter=v_counter+1;
end while;
commit;
end #
delimiter ;
Call procedure and select data:
call insert_test_data();
select * from test_data order by id;
Note: "While ... Do" work only in procedure
I hope it helped you :)
SEND BITCOIN TIPS
User rating:
User rating:
FavScripts.com is a free tool to save your favorite scripts and commands, then quickly find and copy-paste your commands with just few clicks.
Boost your productivity with FavScripts.com!
