How to select and insert values in mysql using stored procedures

Question

I am new in using stored procedures. I have this query that gets values from tables.

After that, I need to insert the result to another table.

Here's my query:

   SELECT a.gender, 
          b.purpose_abroad_as_per_recorded_travel, 
          b.country_name 
   FROM b   LEFT JOIN a
   ON b.person_id=a.id

and i am planning to insert all the results to table 'c'.

How can i do the select and insert simultaneously using stored procedure? thanks

Answer 1

You can insert the results returned from the select directly into the insert:

DELIMITER //
CREATE PROCEDURE updateTableC()
BEGIN       
       INSERT INTO c (gender, purpose_abroad_as_per_recorded_travel, country_name)
       SELECT a.gender, b.purpose_abroad_as_per_recorded_travel, b.country_name 
       FROM b   LEFT JOIN a
       ON b.person_id=a.id;
END//
DELIMITER ;

For more information on MySQL stored procedures this is a good start: Getting Started with MySQL Stored Procedures.

Answer 2

Try this:

INSERT INTO c (gender, purpose_abroad_as_per_recorded_travel, country_name )
SELECT a.gender, b.purpose_abroad_as_per_recorded_travel, b.country_name 
FROM b 
LEFT JOIN a ON b.person_id = a.id;