Apache Derby: Achieving 'SELECT INTO' behaviour

Question

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 1

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;

Answer 2

It does not work in JAVA DB, the correct way to do it is:

For all columns:

Step 1: Create a new table with a different name. for example, my_new_table:

CREATE TABLE my_new_table AS SELECT * FROM original_table WITH NO DATA;

This statement creates a new table from original table in the same format and no data copied. It is required to specify WITH NO DATA for it creates a new table with the same columns.

Step 2: Copy data from orig_table to my_new_table using INSERT INTO.

INSERT INTO my_new_table  SELECT * FROM orig_table.

Then you will have all the data copied.