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.
3 Answers
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
Comments
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;
1 Comment
extraspecialbitter
your answer worked perfectly and was simple enough for this newbie to understand. Thank you!
INSERT INTO archive_master VALUES (SELECT * FROM archive_2013);
