Search code examples
javasqljdbcderby

Apache Derby: Achieving 'SELECT INTO' behaviour

It is possible in MS SQL Server to store the results of query into a table, and most importantly, have the query create the table:

SELECT an_existing_column
INTO table_xyz
FROM an_existing_table

This is also possible in MySQL using:

CREATE TABLE table_xyz
SELECT an_existing_column
FROM an_existing_table

I have searched the Apache Derby Reference Guide and cannot see a method for achieving similar behaviour.

Does anyone know if this possible in Apache Derby?

Solution

  • Store the results of a query into a table:

    INSERT INTO table_xyz (an_existing_column) SELECT an_existing_column FROM an_existing_table;

    Create a table from another table:

    All Columns:

    CREATE TABLE table_xyz AS SELECT * FROM an_existing_table WITH NO DATA;

    Specific Column:

    CREATE TABLE table_xyz AS SELECT an_existing_column FROM an_existing_table WITH NO DATA;