SELECT * INTO - MYSQL

Question

I'm trying to move a select statement's result into a new non-existent table but not able to figure how.

In MS SQL, I would be following the below,

SELECT * INTO <NON_EXISTING_TABLE> FROM
(
SELECT * FROM TABLE1 A 
JOIN TABLE2 B
ON A.DescriptionNo = B.DescriptionNo 
WHERE A.DescriptionNo =1) A

When i quickly looked it up , I can see only answers to insert data into an existing table but not dynamically create a new table with the result of the statement.

Please advice !

I guess you are looking for [Create a temporary table in a SELECT statement without a separate CREATE TABLE](https://stackoverflow.com/questions/5859391/create-a-temporary-table-in-a-select-statement-without-a-separate-create-table) — M Khalid Junaid, Nov 29 '17 at 10:11
https://stackoverflow.com/questions/16809393/select-into-in-mysql — etsa, Nov 29 '17 at 10:12
Sorry, my bad. The answer was right there and I had missed it. Thanks for your responses though! — Chendur, Nov 29 '17 at 10:23

score 4 · Answer 1 · answered Nov 29 '17 at 10:12

4

If you want to create a new table from a select, you can use this syntax:

create table new_table as
select *
from existing_table
where ...

answered Nov 29 '17 at 10:12

fthiella

48,073
15
90
106

Don't know who down voted this but according to dupe question it is the right solution – M Khalid Junaid Nov 29 '17 at 10:15

score 1 · Accepted Answer · answered Nov 29 '17 at 10:13

This solution showed above works perfect also for selected rows. For example I am creating demonstration rows for my nice2work project, and this works perfect.

CREATE TEMPORARY TABLE tmptable SELECT * FROM myTable WHERE id=500;
UPDATE tmptable SET id = 0;
UPDATE some fields I need to change
INSERT INTO myTable SELECT * FROM tmptable;
DROP TABLE tmptable;

//  You can use this same also directly into your code like (PHP Style)
$sql = "CREATE TEMPORARY TABLE tmptable SELECT * FROM myTable WHERE id=500;
UPDATE tmptable SET id = 0;
UPDATE some fields I need to change
INSERT INTO myTable SELECT * FROM tmptable;DROP TABLE tmptable;";

SELECT * INTO - MYSQL

2 Answers2