Insert random number of rows into MySQL table using just SQL / stored routine

0

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?

Thanks!

Tags: ,

1 Answer

Try this steps:

  1. 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;
    
  2. 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 ;
    
  3. 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 :)

0

Post Answer