Copying Rows from One Table to Another (INSERT INTO ... select ...) : Column Copy « Insert Update Delete « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Insert Update Delete
3. Column Copy

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>