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