Select firstname as name, time as asof, salary as bal into temp employee
from people.person p
where p.id =1;

Need to create a temporary table employee by inserting values from already created table person which belongs to people database but getting error from keyword not found where expected

CodePudding user response：

You'd then use CTAS (Create Table As Select), not an invalid INTO clause; it is used for different purposes.

create table temp_employee as
  select firstname as name,
         time as asof,
         salary as bal
  from people.person p
  where p.id = 1;

Based on comment you posted, there are several options you might want to consider.

One is to create a permanent table (just like the above example shows). If you'll reuse it, then - in your procedure - first delete its contents (or truncate the table as it is way faster), and then re-populate it:

delete from temp_employee;
-- or truncate table temp_employee;

insert into temp_employee
  select firstname, time, salary from people.person
  where id = 1;

Another option is to create a true temporary table, e.g.

create global temporary table temp_employee
  (name     varchar2(30),
   time     date,
   bal      number
  )
on commit preserve rows;

Whenever you need data in it, just insert it:

insert into temp_employee (name, time, bal)
select firstname as name,
       time as asof,
       salary as bal
from people.person p
where p.id = 1;

Doing so, its contents will be visible only to you (and nobody else), while table's contents will be kept during the transaction or session (it depends on how you created it - see the on commit preserve/delete rows clause).

What you should not do is to create the table, drop it, then create it again, and so on - in Oracle, we create table once and use it many times.