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.

mahmud ahsan

Love computer programming and write occasional blog post. In my early career I worked mostly on web based application and since 2011, I exclusively working on iPhone and android applications. I work at home, father of a nice kid and husband of a beautiful wife. [My Business | Twitter | Linkedin]

You may also like

4 Comments

  • munkh
    November 2, 2012 at 5:47 pm

    thanks

  • 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

  • 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!

  • Ankit
    June 26, 2013 at 5:32 pm

    Good one

Comments are closed here.

About Me

Hi I am Mahmud Ahsan. I Love computer programming and write occasional blog post. In my early career I worked mostly on web based application and since 2011, I exclusively working on iPhone and android applications. I work at home, father of a nice kid and husband of a beautiful wife. [My Business | Twitter | Linkedin]

Twitter Feed

Fanpage