How to populate a table from multiple tables in stored procedures?

This question is generic, what I’m trying to do is maybe simple but I’m a bit stuck.

I have multiple tables to join with each other into one table. Now I have created a staging table (column names, datatypes). What I’m trying to do is, I want to create a stored procedure logic to join these tables and pump into the new table that I have created.

I’ve tried a few ways from the StackOverflow’s solutions but cannot find what I’m trying to do. If you can help me or even give me the right links to refer, it will be good. Thank you in advance.
from StackOverflow question

Answer:

Here’s an example of a stored procedure that joins multiple tables and inserts the results into a new table:

DELIMITER $$
CREATE PROCEDURE join_and_insert()
BEGIN
    -- Joining tables 
    SELECT 
        t1.col1, t1.col2, t2.col3, t3.col4
    INTO 
        @temp_table
    FROM 
        table1 t1 
        JOIN table2 t2 ON t1.id = t2.id 
        JOIN table3 t3 ON t2.id = t3.id;
   
    -- Inserting data into new table
    INSERT INTO new_table
        SELECT * FROM @temp_table;
   
    -- drop temp table
    DROP TEMPORARY TABLE @temp_table;
END$$
DELIMITER ;

In this example, the stored procedure first performs a SELECT statement that joins the tables table1, table2, and table3 on the id column. The result of this join is stored in a temporary table called @temp_table.

Then it inserts the data from the temporary table @temp_table into the new table new_table.

Finally, the temporary table is dropped.

You can then call this stored procedure whenever you need to update the data in the new table.

CALL join_and_insert();

Please Note that the above example is just a skeleton and you should adjust the column names, table names, join conditions and other details to match your database schema. Also, you should consider testing the Stored procedure in a testing environment before applying it to a production environment.

Also, if you have a huge data set and the table is already populated with data, you might need to consider other options like partitioning the data, or creating a materialized view instead of a table to improve the performance.