In MySQL, how to copy the content of one table to another table within the same database?

sql mysql database
Joneph O. picture Joneph O. · Feb 26, 2010 · Viewed 120.1k times · Source

I am new to MySQL. I would like to copy the content of one table to another table within the same database. Basically, I would like to insert to a table from another table. Is there easy way of doing this?

Answer

ggiroux picture ggiroux · Feb 26, 2010

INSERT INTO TARGET_TABLE SELECT * FROM SOURCE_TABLE;

EDIT: or if the tables have different structures you can also:

INSERT INTO TARGET_TABLE (`col1`,`col2`) SELECT `col1`,`col2` FROM SOURCE_TABLE;

EDIT: to constrain this..

INSERT INTO TARGET_TABLE (`col1_`,`col2_`) SELECT `col1`,`col2` FROM SOURCE_TABLE WHERE `foo`=1

Related questions

'IF' in 'SELECT' statement - choose output value based on column values

SELECT id, amount FROM report I need amount to be amount if report.type='P' and -amount if report.type='N'. How do I add this to the above query?

Read More
MySQL Error: : 'Access denied for user 'root'@'localhost'

$ ./mysqladmin -u root -p 'redacted' Enter password: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: YES)' How can I fix this?

Read More
How do I restore a dump file from mysqldump?

I was given a MySQL database file that I need to restore as a database on my Windows Server 2008 machine. I tried using MySQL Administrator, but I got the following error: The selected file was generated by mysqldump and cannot …

Read More