How to copy multiple tables (with same columns names) to a new table <SQL>.?

copy, mysql

Solution

I guess you want to use `VIEW`

CREATE VIEW new_Table
AS
SELECT  p1, p2, op1, op2 FROM t1
UNION ALL
SELECT  p1, p2, op1, op2 FROM t2
UNION ALL
SELECT  p1, p2, op1, op2 FROM t3;

if either of the tables are updated, it automatically reflects on the view.

- VIEW

Problem

How to copy multiple tables (with same columns names) to a new table `<SQL>`? Like: ``` CREATE TABLE t1 ( p1 longtext, p2 longtext, op1 varchar op2 varchar, ); CREATE TABLE t2 ( p1 longtext, p2 longtext, op1 varchar op2 varchar, ); CREATE TABLE t3 ( p1 longtext, p2 longtext, op1 varchar op2 varchar, ); ``` What I am hoping to achieve is trying to copy all the above table t1,t2,t3 into a new table new_table. Something like : (sql is wrong) ``` CREATE TABLE new_table AS (SELECT p1,p2,op1,op2) FROM t1,t2,t3); ``` Also, if I get the new table created, I am expecting every time when the tables t1,t2,t3 is updated simultaneity the new_table also gets updated. - Do i need to use a trigger for this? Please help me solve this.

Original source