Insert data into the temporary table. : Temporary Table « Table « Oracle PL / SQL

Oracle PL / SQL
1. Aggregate Functions
2. Analytical Functions
3. Char Functions
4. Constraints
5. Conversion Functions
6. Cursor
7. Data Type
8. Date Timezone
9. Hierarchical Query
10. Index
11. Insert Delete Update
12. Large Objects
13. Numeric Math Functions
14. Object Oriented Database
15. PL SQL
16. Regular Expressions
17. Report Column Page
18. Result Set
19. Select Query
20. Sequence
21. SQL Plus
22. Stored Procedure Function
23. Subquery
24. System Packages
25. System Tables Views
26. Table
27. Table Joins
28. Trigger
29. User Previliege
30. View
31. XML
Java
Java Tutorial
Java Source Code / Java Documentation
Java Open Source
Jar File Download
Java Articles
Java Products
Java by API
Photoshop Tutorials
Maya Tutorials
Flash Tutorials
3ds-Max Tutorials
Illustrator Tutorials
GIMP Tutorials
C# / C Sharp
C# / CSharp Tutorial
C# / CSharp Open Source
ASP.Net
ASP.NET Tutorial
JavaScript DHTML
JavaScript Tutorial
JavaScript Reference
HTML / CSS
HTML CSS Reference
C / ANSI-C
C Tutorial
C++
C++ Tutorial
Ruby
PHP
Python
Python Tutorial
Python Open Source
SQL Server / T-SQL
SQL Server / T-SQL Tutorial
Oracle PL/SQL Tutorial
PostgreSQL
SQL / MySQL
MySQL Tutorial
VB.Net
VB.Net Tutorial
Flash / Flex / ActionScript
VBA / Excel / Access / Word
XML
XML Tutorial
Microsoft Office PowerPoint 2007 Tutorial
Microsoft Office Excel 2007 Tutorial
Microsoft Office Word 2007 Tutorial
Oracle PL / SQL » Table » Temporary Table 
Insert data into the temporary table.
   
SQL>
SQL> create table employee(
  2           emp_no                 integer         primary key
  3          ,lastname               varchar2(20)    not null
  4          ,firstname              varchar2(15)    not null
  5          ,midinit                varchar2(1)
  6          ,street                 varchar2(30)
  7          ,city                   varchar2(20)
  8          ,state                  varchar2(2)
  9          ,zip                    varchar2(5)
 10          ,zip_4                  varchar2(4)
 11          ,area_code              varchar2(3)
 12          ,phone                  varchar2(8)
 13          ,salary                 number(3)
 14          ,birthdate              date
 15          ,hiredate               date
 16          ,title                  varchar2(20)
 17          ,dept_no                integer
 18          ,mgr                    integer
 19          ,region                 number
 20          ,division               number
 21          ,total_sales            number
 22  );

Table created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (1,'Anderson','Nancy','N','33 Ave','London','NY','11111','1111','212','234-1111',3.75,'21-mar-1927','1-feb-1947','Sales Manager',2,null,100,10,40000);

row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (2,'Last','First','F','12 Ave','Paris','CA','22222','2222','221','867-2222',7.75,'14-feb-1976','15-mar-1985','Sales Clerk',2,1,100,10,10000);

row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (3,'Wash','Georgia','G','Street14','Barton','NJ','33333','3333','214','340-3333',11.50,'2-jul-1977','21-apr-2004','Designer',1,2,100,10,40000);

row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (4,'Bush','Dave','D','56 Street','Island','RI','44444','4444','215','777-4444',21.65,'15-may-1945','2-aug-1975','Designer',1,2,100,10,40000);

row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (5,'Will','Robin','W','56 Street','Island','MA','55555','5555','216','777-5555',24.65,'10-dec-1980','2-aug-2007','Designer',1,5,100,10,40000);

row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (6,'Pete','Mona','M','13 Ave','York','MO','66666','6666','217','111-6666',9,'14-feb-1966','15-mar-1985','Sales Clerk',2,5,100,10,40000);

row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (7,'Roke','John','J','67 Ave','New York','BC','77777','7777','218','122-7777',10.00,'14-jun-1955','15-mar-1975','Accountant',3,2,100,10,40000);

row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (8,'Horry','Tedi','T','1236 Lane','Newton','NY','88888','8888','219','222-8888',13.00,'10-jun-1955','15-aug-1985','Sales Representative',3,2,100,10,50000);

row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2                values (9,'Bar','Candi','C','400 East Street','Yorken','NY','99999','9999','220','321-9999',12.00,'10-oct-1933','15-jan-1969','Sales Representative',3,5,100,10,35000);

row created.

SQL>
SQL> select from employee;
    EMP_NO LASTNAME             FIRSTNAME       M STREET                         CITY                 ST ZIP   ZIP_ ARE PHONE        SALARY
---------- -------------------- --------------- - ------------------------------ -------------------- -- ----- ---- --- -------- ----------
BIRTHDATE            HIREDATE             TITLE                   DEPT_NO        MGR     REGION   DIVISION TOTAL_SALES
-------------------- -------------------- -------------------- ---------- ---------- ---------- ---------- -----------
         Anderson             Nancy           N 33 Ave                         London               NY 11111 1111 212 234-1111          4
21-MAR-1927 00:00:00 01-FEB-1947 00:00:00 Sales Manager                 2                   100      10  40000

         Last                 First           F 12 Ave                         Paris                CA 22222 2222 221 867-2222          8
14-FEB-1976 00:00:00 15-MAR-1985 00:00:00 Sales Clerk                   2          1        100      10  10000

         Wash                 Georgia         G Street14                     Barton               NJ 33333 3333 214 340-3333         12
02-JUL-1977 00:00:00 21-APR-2004 00:00:00 Designer                      1          2        100      10  40000

         Bush                 Dave            D 56 Street                      Island               RI 44444 4444 215 777-4444         22
15-MAY-1945 00:00:00 02-AUG-1975 00:00:00 Designer                      1          2        100      10  40000

         Will                 Robin           W 56 Street                      Island               MA 55555 5555 216 777-5555         25
10-DEC-1980 00:00:00 02-AUG-2007 00:00:00 Designer                      1          5        100      10  40000

         Pete                 Mona            M 13 Ave                         York                 MO 66666 6666 217 111-6666          9
14-FEB-1966 00:00:00 15-MAR-1985 00:00:00 Sales Clerk                   2          5        100      10  40000

         Roke                 John            J 67 Ave                         New York             BC 77777 7777 218 122-7777         10
14-JUN-1955 00:00:00 15-MAR-1975 00:00:00 Accountant                    3          2        100      10  40000

         Horry                Tedi            T 1236 Lane                      Newton               NY 88888 8888 219 222-8888         13
10-JUN-1955 00:00:00 15-AUG-1985 00:00:00 Sales Representative          3          2        100      10  50000

         Bar                  Candi           C 400 East Street                Yorken               NY 99999 9999 220 321-9999         12
10-OCT-1933 00:00:00 15-JAN-1969 00:00:00 Sales Representative          3          5        100      10  35000


rows selected.

SQL>
SQL> CREATE GLOBAL TEMPORARY TABLE temp_emp
  2    AS SELECT FROM employee;

Table created.

SQL>
SQL> INSERT INTO temp_emp SELECT FROM employee;

rows created.

SQL> select from temp_emp;
    EMP_NO LASTNAME             FIRSTNAME       M STREET                         CITY                 ST ZIP   ZIP_ ARE PHONE        SALARY
---------- -------------------- --------------- - ------------------------------ -------------------- -- ----- ---- --- -------- ----------
BIRTHDATE            HIREDATE             TITLE                   DEPT_NO        MGR     REGION   DIVISION TOTAL_SALES
-------------------- -------------------- -------------------- ---------- ---------- ---------- ---------- -----------
         Anderson             Nancy           N 33 Ave                         London               NY 11111 1111 212 234-1111          4
21-MAR-1927 00:00:00 01-FEB-1947 00:00:00 Sales Manager                 2                   100      10  40000

         Last                 First           F 12 Ave                         Paris                CA 22222 2222 221 867-2222          8
14-FEB-1976 00:00:00 15-MAR-1985 00:00:00 Sales Clerk                   2          1        100      10  10000

    EMP_NO LASTNAME             FIRSTNAME       M STREET                         CITY                 ST ZIP   ZIP_ ARE PHONE        SALARY
---------- -------------------- --------------- - ------------------------------ -------------------- -- ----- ---- --- -------- ----------
BIRTHDATE            HIREDATE             TITLE                   DEPT_NO        MGR     REGION   DIVISION TOTAL_SALES
-------------------- -------------------- -------------------- ---------- ---------- ---------- ---------- -----------

         Wash                 Georgia         G Street14                     Barton               NJ 33333 3333 214 340-3333         12
02-JUL-1977 00:00:00 21-APR-2004 00:00:00 Designer                      1          2        100      10  40000

         Bush                 Dave            D 56 Street                      Island               RI 44444 4444 215 777-4444         22
15-MAY-1945 00:00:00 02-AUG-1975 00:00:00 Designer                      1          2        100      10  40000

         Will                 Robin           W 56 Street                      Island               MA 55555 5555 216 777-5555         25
10-DEC-1980 00:00:00 02-AUG-2007 00:00:00 Designer                      1          5        100      10  40000

         Pete                 Mona            M 13 Ave                         York                 MO 66666 6666 217 111-6666          9
14-FEB-1966 00:00:00 15-MAR-1985 00:00:00 Sales Clerk                   2          5        100      10  40000

         Roke                 John            J 67 Ave                         New York             BC 77777 7777 218 122-7777         10
14-JUN-1955 00:00:00 15-MAR-1975 00:00:00 Accountant                    3          2        100      10  40000

         Horry                Tedi            T 1236 Lane                      Newton               NY 88888 8888 219 222-8888         13
10-JUN-1955 00:00:00 15-AUG-1985 00:00:00 Sales Representative          3          2        100      10  50000

         Bar                  Candi           C 400 East Street                Yorken               NY 99999 9999 220 321-9999         12
10-OCT-1933 00:00:00 15-JAN-1969 00:00:00 Sales Representative          3          5        100      10  35000


rows selected.

SQL>
SQL> commit;

Commit complete.

SQL> select from temp_emp;

no rows selected

SQL> commit;

Commit complete.

SQL>
SQL> drop table employee;

Table dropped.

SQL> drop table temp_emp;

Table dropped.

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 into a temporary with select statement
14. truncate a global temporary table
15. Using Temporary Tables
16. Create global temporary table working_emps on commit preserve rows
www.java2java.com | Contact Us
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.