hmjd hmjd - 5 months ago 18
Java Question

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?

Answer

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;