how to generate dummy data in mysql or sql

    By: Manu
    5 months ago

    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