Inserting values from two different tables into another in MySQL

Question

I want to fill one of my tables with values form two different tables (it's going to be a dimension for my OLAP cube). So I have tables

 Birth_Act (
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 citizen_id INT,
 birth_date DATETIME,
 place VARCHAR(20)
 );

 Death_Act (
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 deceased_id INT,
 death_date DATETIME,
 place VARCHAR(20)
 );

I want to simplify this to one table Act with the following structure:

 Act (
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 year INT,
 month INT,
 place VARCHAR(20),
 type VARCHAR(20),
 );

Now I want to create a procedure which would insert into table Act values: year - year of birthdate from Birth_Act table, month - month of birthdate from the same table, place - place from the Birth_Act table and then type - birth. After this I would like it to do the same with Death_Act. Is it possible to do in a single query? I tried doing it the following way:

  create procedure fillAct()
begin
  declare i int Default 0;
   myloop: loop
    INSERT INTO Act (id, type, year, month, place) VALUES 
    (i+1, "Birth", (SELECT YEAR(birth_date) FROM Birth_Act LIMIT 1), 
    (SELECT MONTH(birth_date) FROM Birth_Act LIMIT 1),
    (SELECT place FROM Birth_Act LIMIT 1));
    set i=i+1;
    if i=10000 then
    leave myloop;
end if;
end loop myloop;
   end $$

Then I wanted to do the same with Death Acts starting from i = 10000. But obviously this doesn't do the job and seems like a bad idea.

score 1 · Accepted Answer · answered Jun 16 '14 at 18:43

Do a UNION of 2 SELECTS from 2 tables and then insert them into 3rd table, all in one query, like this:

INSERT INTO Act (type, year, month, place)
SELECT 'Birth', YEAR(birth_date), MONTH(birth_date), place FROM Birth_Act
UNION
SELECT 'Death', YEAR(death_date), MONTH(death_date), place FROM Death_Act

Note that ID is primary key with AUTO_INCREMENT so no need to set IDs yourself.

Inserting values from two different tables into another in MySQL

1 Answers1