How To Load Data from External Tables to Regular Tables? - Oracle DBA FAQ - Loading and Exporting Data

Once you have your data entered in a text file, and an external table defined to this text file, you can easily load data from this text file to a regular table. The following tutorial exercise shows you how to load data from the text file and the external table defined in the previous exercises to a regular table:

SQL> CREATE TABLE fyi_links (
    id NUMBER(4) PRIMARY KEY,
    url VARCHAR2(16) NOT NULL,
    notes VARCHAR2(16),
    counts NUMBER(4),
    created DATE DEFAULT (sysdate)
  );

SQL> INSERT INTO fyi_links SELECT * FROM ext_fyi_links;
2 rows created.

SQL> SELECT * FROM fyi_links;
       ID URL              NOTES         COUNTS CREATED
--------- ---------------- ----------- -------- ---------
      101 fyicenter.com    Session 1            07-MAY-06
      110 centerfyi.com    Session 1            07-MAY-06
     1101 dba.fyicenter    Link #1           88 07-MAY-06
     1110 dev.fyicenter    Link #2           88 07-MAY-06