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

$0
=
$0
+
$0
No specific Bitcoin Bounty has been announced by author. Still, anyone could send Bitcoin Tips to those who provide a good answer.
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 - total earned - 0 BTC ($0 USD)

1
=
0
=
$0
Everyone, even non-registered users could send Bitcoin Tips to YOU if they are inspired by 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)
    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 :)

SEND BITCOIN TIPS
0

Post Answer


Will Bitcoin Tipping become a new Internet Culture?


Please share our story! THANK YOU!