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:
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?
Try this steps:
drop table if exists test_data; create table test_data (id int unsigned not null auto_increment primary key, create_date datetime) engine=innodb;
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 :)
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!