Oracle PL/SQL Tutorial/Insert Update Delete/Column Copy

Copying Rows from One Table to Another (INSERT INTO ... select ...)

The number of columns and the column types in the source and destination must match.

SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    EMPNO         NUMBER(3),
  3    ENAME         VARCHAR2(15 BYTE),
  4    HIREDATE      DATE,
  5    ORIG_SALARY   NUMBER(6),
  6    CURR_SALARY   NUMBER(6),
  7    REGION        VARCHAR2(1 BYTE),
  8    MANAGER_ID    NUMBER(3)
  9  )
 10  /
Table created.
SQL>
SQL> create table job (
  2    EMPNO         NUMBER(3),
  3    jobtitle      VARCHAR2(20 BYTE)
  4  )
  5  /
Table created.
SQL>
SQL> insert into job (EMPNO, Jobtitle) values (1,"Tester");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (2,"Accountant");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (3,"Developer");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (4,"COder");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (5,"Director");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (6,"Mediator");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (7,"Proffessor");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (8,"Programmer");
1 row created.
SQL> insert into job (EMPNO, Jobtitle) values (9,"Developer");
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from job
  2  /
     EMPNO JOBTITLE
---------- --------------------
         1 Tester
         2 Accountant
         3 Developer
         4 COder
         5 Director
         6 Mediator
         7 Proffessor
         8 Programmer
         9 Developer
9 rows selected.
SQL>
SQL> INSERT INTO employee (empno)
  2  SELECT empno
  3  FROM job;


SQL>
SQL> -- clean the table
SQL> drop table Employee
  2  /
Table dropped.
SQL> drop table job
  2  /
Table dropped.
SQL>
SQL>