CODEWITHSUNDEEP
mysql
The Old County
The Old County

Reputation: 89

Merging multiple sql table results into one

I have imported several large csv's and I am looking to create a merged table from several imports.

So lets say I have 2 tables.

table1:

title
ben
rupert

table2:

title
karen
jill

and I want to either populate an empty table or create one on the fly.

//NewTable

title
ben
rupert
karen
jill

I've tried using SQL like this - but I am getting NewTable undefined variable issues

select * 
into `NewTable`
from(
    select * from `table1`
    union all
    select * from `table2`
    union all
)t

Upvotes: 0

Views: 52

Answers (3)

Reece Kenney
Reece Kenney

Reputation: 2964

Create NewTable first then:

INSERT INTO NewTable
SELECT * FROM table1
UNION ALL
SELECT * FROM table2

An alternate way in which you wouldn't need to create the table first, off the top of my head would be:

SELECT *
INTO NewTable
FROM table1

And then perform the insert from the second table:

INSERT INTO NewTable
SELECT * FROM table2

Upvotes: 2

Parag Deshmukh
Parag Deshmukh

Reputation: 23

insert into table1 select * from table2;

Upvotes: 0

Shadow
Shadow

Reputation: 34232

The select ... into ... statement in MySQL is for populating variables. What you are looking for is the insert ... select ... statement:

insert into newtable
select * from ...

Upvotes: 0

Related Questions