Using MySQL Stored Procedure to create sample data

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