MySQL – Insert Unique IDs from Several Tables into Events Table

mariadbMySQL

I have a table called events defined as follow:

enter image description here

rep_id, hcp_id, brand_id, email_id and content_id is the IDs in other tables.

event_type should be kind of array containing this values:

[
    "rep_login",
    "rep_share",
    "email_viewed",
    "brand_page_opened",
    "media_viewed",
    "rep_contacted",
]

I need to insert 10k rows on events table but compliance with these conditions:

  • Each row should contains UNIQUE values for each column
  • event_type should be randomize for populate the table with several event types

This data is for testing purpose. Can this be done from MySQL query? How?

Best Answer

You can use this insert statement to insert some totally useless data in there (assuming the FK accuracy is not important here) but it satisfies your conditions.

    DELIMITER $$
DROP PROCEDURE IF EXISTS Events_insertion$$
CREATE PROCEDURE Events_insertion()
BEGIN
    DECLARE i  INT;
    DECLARE dTime DATETIME;
    SET i = 1;
    SET dTime = NOW();
    WHILE i  <= 10000 DO
       INSERT INTO EVENTS_TEST VALUES
        (
        null,
        (ELT(0.5 + RAND() * 6, "rep_login",
            "rep_share",
            "email_viewed",
            "brand_page_opened",
            "media_viewed",
            "rep_contacted")),
        LAST_INSERT_ID()+2,
        LAST_INSERT_ID()+3,
        LAST_INSERT_ID()+4,
        LAST_INSERT_ID()+5,
        LAST_INSERT_ID()+6,
        (dTime + interval (LAST_INSERT_ID()) second),
        (dTime + interval (LAST_INSERT_ID()) minute)
        );
       SET  i = i + 1; 
    END WHILE;
END$$
DELIMITER ;

You can call this procedure like this

call Events_insertion();

and also try not to use name events as your table name since it is a reserved word. Hope this could help you :)

Related Question