SELECT INTO equivalent in mysql

Question

I have been easily using SELECT * INTO newtable IN 'another_database' FROM original_table_in_separate_database; to backup/copy data from one table to another table easily in MSSQL.

Now i am moving to MYSQL and cannot accomplish this task as this feature is not available in MYSQL.

Though CREATE TABLE ... SELECT can somehow accomplish the task in same database, but not with two different database.

Please help me if there is any idea :)

Thanks in advance .

Answer 1

You can use INSERT INTO .. SELECT FROM construct like

INSERT INTO db1.dbo.newtable 
SELECT * FROM db2.dbo.original_table_in_separate_database;

Point to note: For INSERT INTO .. SELECT to work both the target table and inserting table must exist. Otherwise, use CREATE TABLE AS ... SELECT like

CREATE TABLE newtable
AS
SELECT * FROM db2.dbo.original_table_in_separate_database;