Insert 10000 records using WHILE loop in MySQL

Based on the maximum and minimum parameters provided, the example will insert 10000 records into a table.

The goal of this example is to show beginners how to –

1) construct a WHILE loop statement
2) Develop a stored procedure
3) Insert 10,000 random records for the practice dataset.

— Drop table if exists
DROP TABLE IF EXISTS TestTable;

— Table creation statement
CREATE TABLE IF NOT EXISTS TestTable(Id INT, unqIdentifier TEXT);

— Dropping the procedure in case exists
DROP PROCEDURE IF EXISTS uspInsertProc;

— Creating the procedure since WHILE loop cannot be used in an anonymous block.
CREATE PROCEDURE uspInsertProc()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i <= 10000 DO
INSERT INTO TestTable(Id, unqIdentifier) SELECT i, MD5(RAND());
SET i = i + 1;
END WHILE;
END
;
CALL uspInsertProc();

— Check the data
SELECT * FROM TestTable;

Hope you find this article helpful.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s