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

CREATE TABLE IF NOT EXISTS `dictionary` (
  `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;
DELIMITER $$

DROP PROCEDURE IF EXISTS SampleProc$$
CREATE PROCEDURE SampleProc()
       BEGIN
               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;
       END$$
   DELIMITER ;

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.

About mahmud ahsan

Founder And Lead Programmer at iThinkdiff.net

, , , ,

4 Responses to Using MySQL Stored Procedure to create sample data

  1. munkh November 2, 2012 at 5:47 pm #

    thanks

  2. 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

  3. www.kingwell.com June 7, 2013 at 8:24 pm #

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

  4. Ankit June 26, 2013 at 5:32 pm #

    Good one

Leave a Reply