Menu
I've tried to mix a CREATE TABLE table_name AS SELECT .... statement with a GLOBAL temporary table statement. They don't mix very well.
Is my example wrong?
CREATE GLOBAL TEMPORARY TABLE a AS
(
SELECT * from b
)
ON COMMIT PRESERVE ROWS;
316
After creating the table, we can insert data into it as the persisted tables. At the same time, we can create a temporary table using the SQL SELECT INTO statement command.
By default, rows in a temporary table are stored in the default temporary tablespace of the user who creates it. However, you can assign a temporary table to another tablespace upon creation of the temporary table by using the TABLESPACE clause of CREATE GLOBAL TEMPORARY TABLE .
The WITH clause is an optional clause used to contain one or more common table expressions (CTE) where each CTE defines a temporary table that exists for the duration of the query. Each subquery in the WITH clause specifies a table name, an optional list of column names, and a SELECT statement.
To create a Global Temporary Table, add the “##” symbol before the table name. Global Temporary Tables are visible to all connections and Dropped when the last connection referencing the table is closed. Global Table Name must have an Unique Table Name.
it should be:
CREATE GLOBAL TEMPORARY TABLE a
ON COMMIT PRESERVE ROWS
AS
select * from b;
(add where 1=0 too if you didn't want to initially populate it for the current session with all the data from b).
59
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
