2 Top SQL Queries to Create Temp Table in Oracle

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.

Photo by Element5 Digital on Unsplash

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

NAMECUST_IDSRINI100001AARTI1022201Customer 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.

NAMECUST_IDSRINI100001AARTI1022201my_temp1 table

Spotlight: Here are two ways to create a temporary table in Oracle.

Read more articles @srinimf.com