Search code examples
mysqlsqlcreate-table

Is there a 'SHOW CREATE TABLE' for another query

I know SHOW CREATE TABLE gives you the script in order to create a table based on an existing table but I would like to get the script for the return of a query.

Example : I have lots of really long and complex queries, but now I would like to create tables for what these queries fetch.

Is there a way to do this?

Solution

  • To get the creation script, you can put the results of your query into a temporary table, then use SHOW CREATE TABLE:

    CREATE TEMPORARY TABLE query1 AS
    SELECT * FROM YourQuery
    LIMIT 0; --Add LIMIT 0 to avoid putting all the data into the table TY Bill Karwin

SHOW CREATE TABLE query1;

    If you can access the end destination for the table (and you want the results included), you can skip a step and just use CREATE..SELECT:

    CREATE TABLE permanent1 AS
    SELECT * FROM YourQuery;