In this guide we are going to generate dummy data, that you can use in your projects. We are going to use phpmyadmin to generate data. If your system has less ram you still be able to generate big number of rows in your database table. Lets dive in and see how you can accomplish this
First this First
Open phpmyadmin or any other database app that you use, In this example i will use phpmyadmin.
there you need to select a database table
or run this query
CREATE TABLE Employee ( EmployeeID INT AUTO_INCREMENT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50), Email VARCHAR(100), PhoneNumber VARCHAR(20), Department VARCHAR(50), Salary DECIMAL(10, 2), HireDate DATE );
Adjust it as you wish.
then select employees table and then select "sql" tab to run sql queries.
There add. this sql code to create stored procedure.
DELIMITER $$
CREATE PROCEDURE InsertLargeSampleData()
BEGIN
DECLARE counter INT DEFAULT 1;
WHILE counter <= 10000 DO
INSERT INTO Employee (FirstName, LastName, Email, PhoneNumber, Department, Salary, HireDate)
VALUES (
CONCAT('First', counter),
CONCAT('Last', counter),
CONCAT('email', counter, '@example.com'),
CONCAT('555-555-', LPAD(counter, 4, '0')),
CONCAT('Department ', (counter % 10 + 1)),
ROUND((counter * 1000 + 50000), 2),
DATE_ADD('2023-01-01', INTERVAL (counter % 365) DAY)
);
SET counter = counter + 1;
END WHILE;
END $$
DELIMITER ;
Now run this procedure as many times you want it will add 10k rows each time in your database.
you can make names random as well for this you cane create procedure like this
DELIMITER $$
CREATE PROCEDURE InsertLargeSampleData()
BEGIN
DECLARE counter INT DEFAULT 1;
WHILE counter <= 10000 DO
-- Generate random values for all columns
SET @FirstName = SUBSTRING(MD5(RAND()), 1, 8);
SET @LastName = SUBSTRING(MD5(RAND()), 1, 8);
SET @Email = CONCAT(@FirstName, @LastName, '@example.com');
SET @PhoneNumber = CONCAT('555-555-', LPAD(counter, 4, '0'));
SET @Department = CONCAT('Department ', (counter % 10 + 1));
SET @Salary = ROUND((counter * 1000 + 50000), 2);
SET @HireDate = DATE_ADD('2023-01-01', INTERVAL (counter % 365) DAY);
INSERT INTO Employee (FirstName, LastName, Email, PhoneNumber, Department, Salary, HireDate)
VALUES (@FirstName, @LastName, @Email, @PhoneNumber, @Department, @Salary, @HireDate);
SET counter = counter + 1;
END WHILE;
END $$
DELIMITER ;
Now execute procedure from phpmyadmin or run below given command
CALL InsertLargeSampleData(); // where you run sql statements there this command to run.
It will make names in database completely random.
Want data to be more realistic then create a table with random words in it. Here is the query
CREATE TABLE random_words (
id INT AUTO_INCREMENT PRIMARY KEY,
word VARCHAR(50)
);
-- Insert sample random words
INSERT INTO random_words (word) VALUES
('Michael'),
('James'),
('John'),
('Robert'),
('David'),
('William'),
('Joseph'),
('Richard'),
('Thomas'),
('Charles'),
('Daniel'),
('Matthew'),
('Anthony'),
('Mark'),
('Paul'),
('Steven'),
('George'),
('Kenneth'),
('Andrew'),
('Edward'),
('Brian'),
('Ronald'),
('Timothy'),
('Jason'),
('Scott'),
('Eric'),
('Stephen'),
('Nicholas'),
('Larry'),
('Jonathan'),
('Kevin'),
('Frank'),
('Gregory'),
('Jeffrey'),
('Raymond'),
('Gary'),
('Joshua'),
('Dennis'),
('Jerry'),
('Aaron'),
('Douglas'),
('Peter'),
('Randy'),
('Roger'),
('Willie'),
('Walter'),
('Carl'),
('Adam'),
('Gerald'),
('Mary'),
('Jennifer'),
('Linda'),
('Patricia'),
('Elizabeth'),
('Susan'),
('Jessica'),
('Sarah'),
('Karen'),
('Nancy'),
('Lisa'),
('Betty'),
('Dorothy'),
('Helen'),
('Margaret'),
('Ruth'),
('Sandra'),
('Ashley'),
('Emily'),
('Emma'),
('Olivia'),
('Ava'),
('Sophia'),
('Mia'),
('Charlotte'),
('Amelia'),
('Harper'),
('Evelyn'),
('Abigail'),
('Isabella'),
('Grace'),
('Chloe'),
('Ella'),
('Scarlett'),
('Madison'),
('Lily'),
('Avery'),
('Natalie'),
('Hannah'),
('Lillian'),
('Addison'),
('Aubrey'),
('Zoe'),
('Samantha'),
('Victoria'),
('Eleanor'),
('Molly'),
('Lucy'),
('Audrey'),
('Aria');
Ok now adjust Procedure so we get names more relistic
DELIMITER $$
CREATE PROCEDURE InsertLargeSampleData()
BEGIN
DECLARE counter INT DEFAULT 1;
DECLARE first_name_1 VARCHAR(50);
DECLARE first_name_2 VARCHAR(50);
DECLARE last_name VARCHAR(50);
DECLARE email VARCHAR(100);
DECLARE phone_number VARCHAR(20);
DECLARE department VARCHAR(50);
DECLARE salary DECIMAL(10, 2);
DECLARE hire_date DATE;
WHILE counter <= 10000 DO
-- Generate random first and last names
SET first_name_1 = (SELECT word FROM random_words ORDER BY RAND() LIMIT 1);
SET first_name_2 = (SELECT word FROM random_words ORDER BY RAND() LIMIT 1);
SET last_name = (SELECT word FROM random_words ORDER BY RAND() LIMIT 1);
SET email = CONCAT(first_name_1, first_name_2, '@example.com');
SET phone_number = CONCAT('555-555-', LPAD(counter, 4, '0'));
SET department = CONCAT('Department ', (counter % 10 + 1));
SET salary = ROUND((counter * 1000 + 50000), 2);
SET hire_date = DATE_ADD('2023-01-01', INTERVAL (counter % 365) DAY);
INSERT INTO Employee (FirstName, LastName, Email, PhoneNumber, Department, Salary, HireDate)
VALUES (CONCAT(first_name_1, ' ', first_name_2), last_name, email, phone_number, department, salary, hire_date);
SET counter = counter + 1;
END WHILE;
END $$
DELIMITER ;
Run your Procedure
or call Procedure. data will me more realistic
