CODEWITHSUNDEEP
mysql
extraspecialbitter
extraspecialbitter

Reputation: 35

mysql concatenate multiple existing tables into one new table

I've done some research but all of the examples that I've found seem too complicated given what I would like to do. I have multiple tables of archived data by year (e.g. archive_2013, archive_2012, etc.) and would like to create a new master table (archive_master) consisting of all of the data from all of the tables. The tables have no keys and only 2 columns, one varchar(120) and the other char(20). I'm hoping that this is as simple and straightforward as I think it is.

Upvotes: 0

Views: 81

Answers (3)

Roger
Roger

Reputation: 7612

So you want to create one new table?

You could use a simple INSERT INTO with a SELECT

See:

CREATE TABLE with SELECT

http://dev.mysql.com/doc/refman/5.0/en/create-table-select.html

INSERT INTO TABLE with SELECT

http://dev.mysql.com/doc/refman/5.1/en/insert-select.html

Example

You can create a new table:

create table 'xyz' select * from archive_2010;
insert into xyz select * from archive_2011;

Upvotes: 0

BK004
BK004

Reputation: 392

INSERT INTO archive_master VALUES (SELECT * FROM archive_2013);

Upvotes: 0

CodeZombie
CodeZombie

Reputation: 5377

A simple UNION will do the trick:

SELECT col1, col2
FROM archive_2013
UNION ALL
SELECT col1, col2
FROM archive_2012

Combine it with an INSERT and you are done:

INSERT INTO full_archive
SELECT col1, col2
FROM archive_2013
UNION ALL
SELECT col1, col2
FROM archive_2012

Upvotes: 1

Related Questions