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.
<source lang="sql">
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></source>