Using MySQL Stored Procedure to create sample data

mysqlMySQL stored procedures are programs that are stored and can be executed on the MySQL server. You can call stored procedure from any application over a distributed network. Stored procedures provide a means of interacting in a prescribed way with the database without placing any additional traffic on the network. Here I’m describing a stored procedure that I used to create some sample data.

To learn more about stored procedure checkout this link.

Suppose you may need to create a large number of dataset for a table and your table structure looks like this

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `word` varchar(100) NOT NULL,
  `mean` varchar(300) NOT NULL,
  PRIMARY KEY (`id`)

Now you’ve to add 110000 dummy data to this table. You can dump some dummy data to the table by the following way.

use databasename;

               DECLARE x  INT;
               SET x = 1;
               WHILE x  <= 110000 DO
                   insert into dictionary(word, mean) VALUES('a','a mean');
                   SET  x = x + 1;
               END WHILE;

So you created a stored procedure in mysql, now call the procedure

call SampleProc();

This procedure will insert 1,10,000 data in database table ‘dictionary’. So using mysql stored procedure its really very easy to create task specially for mysql.

I created this solution for one of my friend who wanted to check my another problem

In the above code I created a procedure named SampleProc() where I used a while loop. The loop will run 110000 times.

The code also uses the mysql client delimiter command to change the statement delimiter from ; to $$ while the procedure is being defined. This allows the ; delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql itself. Checkout Defining Stored Programs to learn more about stored procedures.

mahmud ahsan

Computer programmer and hobbyist photographer from Bangladesh, lives in Malaysia. My [Github | Business | Twitter | Linkedin | Instagram | 500px]

You may also like


  • munkh
    November 2, 2012 at 5:47 pm


  • edu2004eu
    November 29, 2012 at 7:51 pm

    amazing, yet simple 🙂 I’ve used your example to do something a little more complicated, using CONCAT(“string”,x) to introduce different values into the rows. 5* to this post

    June 7, 2013 at 8:24 pm

    Hi, just wanted to say, I loved this blog post. It was practical. Keep on posting!

  • Ankit
    June 26, 2013 at 5:32 pm

    Good one

Comments are closed here.

About Me

Computer programmer and hobbyist photographer from Bangladesh, lives in Malaysia. My [Github | Business | Twitter | Linkedin | Instagram | 500px]


Muaz and Jayed First Smile Little Angel Trees Horse Ride Maritime Museum in Malacca Dome of Blue Mosque Malaysia Putrajaya Bridge in Sunny Day Blue Mosque in Malaysia

Twitter Feed