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

No specific Bitcoin Bounty has been announced by author. Still, anyone could send Bitcoin Tips to those who provide a good answer.

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?


Tags: ,

1 Answer

Internet users could send Bitcoin Tips to you if they like your 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)
  2. Create procedure:

    drop procedure if exists insert_test_data;
    delimiter #
    create procedure insert_test_data()
         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;
    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 :)


Too many commands? Learning new syntax? 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!

Post Answer