MySQL SELECT INTO Equivalent?

Question

In SQL Server, I can copy tables or temporary tables to new table, using a SELECT* INTO.. syntax.

Someone know how can I make this same action in MySQL?

Answer 1

You probably want to explicitly create the temporary table using CREATE TEMPORARY TABLE:

CREATE TEMPORARY TABLE my_temp_table (...);

The temporary table is connection-specific so no other connection can access it, and the table is dropped when the connection is terminated. Once that is done, you need to use INSERT INTO...SELECT to copy the data into the temporary table:

INSERT INTO my_temp_table (field1, field2)
SELECT field1, field2
FROM my_table;

The full syntax for CREATE TABLE and INSERT INTO...SELECT are on the linked pages.

Edit: Thanks asaph for the correction.

Answer 2

See CREATE TABLE ... AS SELECT .... Note that this will not recreate indexes and foreign keys, as was already noted.