Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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?
                the create table as select ... is standard ANSI SQL. The select .. into ...  is specific to SQL Server.
– a_horse_with_no_name
                Jun 5, 2014 at 10:49
                @a_horse_with_no_name, ta. I started out with Sybase and SQL Server. Clearly learning non-standard extensions...
– hmjd
                Jun 5, 2014 at 10:51
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;
                Excellent, this seems to work. Thank you. Strangely, I cannot find the WITH NO DATA clause in the reference guide (yet). +1
– hmjd
                Jun 5, 2014 at 9:55
                @hmjd Here you can find at least a bit of explanation under the CREATE TABLE ... AS ...  caption.
– user432
                Jun 5, 2014 at 9:56
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.
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.