Hello everyone,

I have a quick one here. How can I create a table using a SELECT statement? I can't find the right syntax...

In Oracle I'd simply do something like
CREATE TABLE table_name AS
SELECT...

Thanks...

I am not sure about V9, but in version 8 you can use the create table like syntax, but the new table will have the same columns as the original.

You can insert into with a select from, but that does not create the table.

There is no way to create a table based on the columns of a query. The "CREATE TABLE mytable_copy LIKE MyTable" syntax only works for tables, views, or nicknames that are in the catalog.

Are you trying to create a table that has the same columns as the query, or are you trying to create a MQT?

Andy

I'm trying to create a table that has the same columns as the query, basically create a physical view.

It is for testing purposes.

I could always create the table and then do a INSERT INTO / SELECT, but the idea of using the output of a query is faster for temporary tables... too bad...

I'm using v8 by the way.

Thanks fellows!

You could create a VIEW based on the query, then create the TABLE based on the VIEW:


CREATE VIEW MyView as (SELECT ...)
CREATE TABLE MyTable LIKE MyView

HTH

Andy

Try
CREATE TABLE Y AS (SELECT * FROM X ) WITH NO DATA;
INSERT INTO Y SELECT * FROM X;

CREATE TABLE Y AS (SELECT * FROM X ) definition only