merge two mysql tables into a third table

Question

I have two mysql tables:

table 1:

    id  name  type
     1    a    123
     2    b    125

table 2:

    id  text
    1   test1
    2   test2

these two tables need to be merged into a third table

table3:

    id name type text

The id is an auto increment id. the first two tables have data that are not related. I mean, row for id=1 in table 1 has nothing to do with the row for id=1 in table two. So, I basically want to write a sql script which would insert values into table 3 to look like this in the end:

table3:

    id name  type   text
    1   a    123
    2   b    125
    3               test1
    4               test2

the ids in the old tables and the new table don't have to match. Just the data from the tables need to be in the new table. I am very new to mysql and if anyone can help me with this, it would be great!

thanks!

Answer 1

It can be done with something like this:

CREATE TABLE Table3 (
  id int auto_increment, 
  name ..., 
  type int, 
  text ...,
  PRIMARY KEY (id)
);

INSERT INTO table3 (name, type, text) 
     SELECT name, type, text FROM (
       SELECT name, type, NULL AS text FROM table1
       UNION ALL
       SELECT NULL as name, NULL as type, text FROM table2) AS t

With auto-increment, we don't need to recount id at all.

Here's an SQL Fiddle for you to play with. )

I actually didn't understand what empty space in your scheme was for, and assumed it's all NULLs. If not, you can just replace NULL in this query with whatever default values you'd like.

Answer 2

Since nothing's related, start with @raina77ow's table, but just use two queries:

INSERT INTO table3 (name, type, text)
SELECT name, type, NULL
from table1;

INSERT INTO table3 (name, type, text)
SELECT NULL, NULL, text
from table2;