Insert into a temporary with select statement

   
SQL>
SQL> CREATE TABLE employees
  2  ( employee_id          number(10)      not null,
  3    last_name            varchar2(50)      not null,
  4    email                varchar2(30),
  5    hire_date            date,
  6    job_id               varchar2(30),
  7    department_id        number(10),
  8    salary               number(6),
  9    manager_id           number(6)
 10  );

Table created.

SQL>
SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
  2                values ( 1001, 'Lawson', 'lawson@g.com', '01-JAN-2002','MGR', 30000,1 ,1004);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
  2                values ( 1002, 'Wells', 'wells@g.com', '01-JAN-2002', 'DBA', 20000,2, 1005 );

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
  2                 values( 1003, 'Bliss', 'bliss@g.com', '01-JAN-2002', 'PROG', 24000,3 ,1004);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1004,  'Kyte', 'tkyte@a.com', SYSDATE-3650, 'MGR',25000 ,4, 1005);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1005, 'Viper', 'sdillon@a .com', SYSDATE, 'PROG', 20000, 1, 1006);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
  2                 values( 1006, 'Beck', 'clbeck@g.com', SYSDATE, 'PROG', 20000, 2, null);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1007, 'Java', 'java01@g.com', SYSDATE, 'PROG', 20000, 3, 1006);

1 row created.

SQL>
SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values( 1008, 'Oracle', 'oracle1@g.com', SYSDATE, 'DBA', 20000, 4, 1006);

1 row created.

SQL>
SQL> select * from employees;

EMPLOYEE_ID LAST_NAME                                          EMAIL                          HIRE_DATE JOB_ID                         DEPARTMENT_ID     SALARY MANAGER_ID
----------- -------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------
       1001 Lawson                                             lawson@g.com                   01-JAN-02 MGR                                        1      30000       1004
       1002 Wells                                              wells@g.com                    01-JAN-02 DBA                                        2      20000       1005
       1003 Bliss                                              bliss@g.com                    01-JAN-02 PROG                                       3      24000       1004
       1004 Kyte                                               tkyte@a.com                    14-JUN-98 MGR                                        4      25000       1005
       1005 Viper                                              sdillon@a .com                 11-JUN-08 PROG                                       1      20000       1006
       1006 Beck                                               clbeck@g.com                   11-JUN-08 PROG                                       2      20000
       1007 Java                                               java01@g.com                   11-JUN-08 PROG                                       3      20000       1006
       1008 Oracle                                             oracle1@g.com                  11-JUN-08 DBA                                        4      20000       1006

8 rows selected.

SQL>
SQL>
SQL> create global temporary table transaction_tab
  2  on commit delete rows
  3  as select * from employees where 1 = 0;

Table created.

SQL>
SQL> insert into transaction_tab select * from employees;

8 rows created.

SQL>
SQL>
SQL> select count(*) from transaction_tab;

  COUNT(*)
----------
         8

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> drop table employees;

Table dropped.

SQL>
SQL> drop table transaction_tab;

Table dropped.

SQL>
SQL>

Related examples in the same category

1.	Create temporary table
2.	Create global temporary table from existing table
3.	Create global temporary table with 'on commit delete rows' option
4.	Temporary tables cannot be forced into logging.
5.	Temporary tables support primary keys.
6.	Temporary tables do not support foreign keys
7.	You cannot alter a temporary table to change its data duration.(drop and recreate)
8.	ORA-14452: attempt to create, alter or drop an index on temporary table already in use
9.	Update a TEMPORARY TABLE and check the table it based on
10.	create global temporary table on commit delete rows
11.	Temporary table on commit preserce and delete
12.	drop global temporary table
13.	Insert data into the temporary table.
14.	truncate a global temporary table
15.	Using Temporary Tables
16.	Create global temporary table working_emps on commit preserve rows

Insert into a temporary with select statement : Temporary Table « Table « Oracle PL / SQL

Related examples in the same category