CODEWITHSUNDEEP
sqloracle-databaseoracle11gtemp-tables
user3475504
user3475504

Reputation: 1

How to insert query result into a global temporary table?

create global temporary table temptbl (id )

 as

  WITH t1(id, REFERENCE_ORDER_ID) AS (

  SELECT id,
         REFERENCE_ORDER_ID
  FROM   CALL_MASTER
  WHERE  REFERENCE_ORDER_ID = '1761' or id = '1761'   -- 1654 1760
  UNION ALL

  SELECT t2.id,
         t2.REFERENCE_ORDER_ID
  FROM   CALL_MASTER t2, t1
  WHERE   t2.id = t1.REFERENCE_ORDER_ID 
  ), tt(id, REFERENCE_ORDER_ID) AS (

  SELECT id,
         REFERENCE_ORDER_ID
  FROM   CALL_MASTER
  WHERE  REFERENCE_ORDER_ID = '1761' or id = '1761'   -- 1654 1760
  UNION ALL

  SELECT t2.id,
         t2.REFERENCE_ORDER_ID
  FROM   CALL_MASTER t2, tt
  WHERE   t2.REFERENCE_ORDER_ID = tt.id
  )
  --insert into temptbl values  

  insert into temptable   select * from  
 --select into temptable  select * from   
(
select distinct id  FROM   t1;
union
select distinct id  FROM   tt; 
);
--insert  into temptable  values(id); 


select * from temptbl;

Upvotes: 0

Views: 7873

Answers (1)

Alex Poole
Alex Poole

Reputation: 191245

While you could create the global temporary table with the create-table-as-select (CTAS) syntax, putting the with clauses in the right place:

create global temporary table temptbl (id) as
with t1(id, reference_order_id) as (
  select id,
         reference_order_id
  from   call_master
  where  reference_order_id = '1761' or id = '1761'   -- 1654 1760
  union all
  select t2.id,
         t2.reference_order_id
  from   call_master t2, t1
  where  t2.id = t1.reference_order_id 
),
tt(id, reference_order_id) as (
  select id,
         reference_order_id
  from   call_master
  where  reference_order_id = '1761' or id = '1761'   -- 1654 1760
  union all

  select t2.id,
         t2.reference_order_id
  from   call_master t2, tt
  where  t2.reference_order_id = tt.id
)
select * from  
(
  select distinct id from t1
  union
  select distinct id from tt
);

it would be more usual to create the GTT once:

create global temporary table temptbl (id number);

and then insert the relevant records for your session:

insert into temptbl  
with t1(id, reference_order_id) as (
  select id,
         reference_order_id
  from   call_master
  where  reference_order_id = '1761' or id = '1761'   -- 1654 1760
  union all
  select t2.id,
         t2.reference_order_id
  from   call_master t2, t1
  where   t2.id = t1.reference_order_id 
),
tt(id, reference_order_id) as (
  select id,
         reference_order_id
  from   call_master
  where  reference_order_id = '1761' or id = '1761'   -- 1654 1760
  union all

  select t2.id,
         t2.reference_order_id
  from   call_master t2, tt
  where   t2.reference_order_id = tt.id
)
select * from  
(
  select distinct id  from   t1
  union
  select distinct id  from   tt
);

Global temporary tables are permanent schema objects; only the data they hold is temporary - private to the session that inserts it to the table. Creating a GTT at runtime suggests you're probably doing something wrong, and either need to create a GTT as a one-off and populate it for each session that uses it, or possibly should be using a completely different mechanism like a PL/SQL collection.

Or possibly that you've come from another RDBMS where this kind of pattern (with GTTs, or private temporary tables, which don't exist in Oracle until version 18c) is necessary but you're doing something that actually doesn't need either a GTT or a collection in Oracle.

Incidentally, union (without all) removes duplicates, so the distinct clauses in the two queries you are unioning are redundant.

Upvotes: 1

Related Questions