Can I create a table from a query formed using with clause?
Add a comment
|
3 Answers
Sure:
CREATE TABLE t
AS
WITH some_data AS (
SELECT 1 as some_value
FROM dual
UNION ALL
SELECT 2
FROM dual
)
SELECT *
FROM some_data
-
7What about with multiple defined tables? I.e. if you have "WITH x as (...), y as (...)"?– KalinCommented Nov 9, 2018 at 23:56
-
Does not work in oracle 12c. Oracle says 'ora-32034: unsupported use of with clause'. Everything after
create table t asshould be placed insideselect * from (...here...). Just like in @saleh helou's answer.– user10325516Commented Apr 23, 2021 at 11:50 -
-
1@Poolka I first had the same error, until I realized that the WITH and SELECT within the CREATE statement can apparently not be within parentheses: instead of
CREATE TABLE t AS ( WITH some_data AS (...) SELECT * FROM some_data ), it should beCREATE TABLE t AS WITH some_date AS (...) SELECT * FROM some_data. Commented Jan 19, 2022 at 10:29 -
@SanderVandenHautte Thank you for the comment. Now i get it. It's so non-intuitive. I used to add outer select query
create as (select * from ( with as ... ))to make it work. Like in the other answer here. Never thought that parentheses must or must not be used around statements.– user10325516Commented Jan 28, 2022 at 9:19
The CREATE TABLE table_name AS statement creates a table based on a select statement. The solution for a with clause will be :
CREATE TABLE t
AS
SELECT * FROM (
WITH some_data AS (
SELECT 1 as some_value
FROM dual
UNION ALL
SELECT 2
FROM dual
)
);
-
1Thats a nearly exact copy of the former answer.... any reason for that? Commented Mar 2, 2018 at 16:00
-
1made sense to me more than the first answer... although it's practically the same. thanks.– Yoav24Commented Jun 11, 2018 at 7:13
For multiple CTE (common table expressions; i.e. multiple WITH clause), I found the same syntax worked. i.e.
CREATE TABLE schema.table_name as
WITH table1 as (SELECT 1),
table2 as (SELECT 2)
select * from table2
will create the table_name in the schema from the select statement
answered Jul 13, 2020 at 4:10
