Create a new table from merging two tables with union

Question

I have two tables with the same columns.

I can merge them with UNION

select * from  table1
union
select * from table2;

How do I create a new table with the same contents and columns as that query?

score 29 · Accepted Answer · answered Feb 15 '12 at 11:20

29

CREATE TABLE new_table
  SELECT * FROM table1
    UNION
  SELECT * FROM table2;

answered Feb 15 '12 at 11:20

Devart

1

Yes Its working i dont know why i was tryng CREATE TABLE new_table( SELECT * FROM table1 UNION SELECT * FROM table2); – Jonathan Raul Tapia Lopez Feb 15 '12 at 11:31

Husain Basrawala · Answer 2 · 2014-01-09T11:56:55.717

7

create table new_table as
select col1, col2 from table1 
union 
select col1, col2 from table2

Make sure you select same set of columns from tables in union.

edited Jan 09 '14 at 11:56

answered Feb 15 '12 at 11:23

Husain Basrawala

2

mysql throws an error if you try to wrap the union query in parenthesis. omitting the parenthesis did the trick for me, as suggested by @devart's answer – s2t2 Jul 19 '13 at 20:58

score 1 · Answer 3 · answered Nov 01 '14 at 19:14

Or even you can explicitly define create table (we generally use in our project).

CREATE TABLE IF NOT EXISTS auditlog (

user_id varchar(30) NOT NULL default '',

user_ip varchar(255) NOT NULL default '',

........ ........

KEY ie1 (user_id) )

union=(auditlog_2,auditlog_3,auditlog_4) engine=merge insert_method=last;

3 Answers3