Fill database tables with a large amount of test data

Question

I need to load a table with a large amount of test data. This is to be used for testing performance and scaling.

How can I easily create 100,000 rows of random/junk data for my database table?

Answer 1

You could also use a stored procedure. Consider the following table as an example:

CREATE TABLE your_table (id int NOT NULL PRIMARY KEY AUTO_INCREMENT, val int); 

Then you could add a stored procedure like this:

DELIMITER $$ CREATE PROCEDURE prepare_data() BEGIN   DECLARE i INT DEFAULT 100;    WHILE i < 100000 DO     INSERT INTO your_table (val) VALUES (i);     SET i = i + 1;   END WHILE; END$$ DELIMITER ; 

When you call it, you'll have 100k records:

CALL prepare_data();