Here’re SQL queries that create a temp table from another in Oracle. The first SQL query creates a temp table’s structure. The second one creates a temp table with data.
Temporary table structure
Here I have created the customer table and inserted rows.
--create table customer (name char(20), cust_id number); --insert into customer values('SRINI', 100001); --insert into customer values('AARTI', 1022201); --select * from customer;
The output from the customer table
SQL query to create a Temporary table
In the SQL query, the global keyword is mandatory. When you run the query, a table my_temp will create that has the customer’s table structure.
create global temporary TABLE my_temp as select * from customer; -- desc my_temp; select count(*) from my_temp;
It displays the table structure and count of rows in the output. The count zero means no rows are present in the temp table. It is how you can create only a temp table structure.
TABLE MY_TEMPResult Set 5 Column Null? Type NAME - CHAR(20) CUST_ID - NUMBER 2 rows selected. Result Set 6 COUNT(*) 0
If we are ever to gain freedom, it must be by conquering nature, never by running away. Cowards never win victories. We have to fight fear and troubles and ignorance if we expect them to flee before us.Swami vivekanada
Temporary table with Data and structure
I have added a statement ‘on commit preserve rows’ that preserves rows. For verification, issue the select statement that displays the table data.
create global temporary TABLE my_temp1 on commit preserve rows as select * from customer; -- select * from my_temp1;
Here’s the output from the my_temp1 table.