Copying Rows from One Table to Another with INSERT

INSERT statement can copy rows from one table to another using a query in the place of the column values. The number of columns and the column types in the source and destination must match.


CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));


INSERT INTO emp VALUES (1, 'Kyle', NULL, NULL, NULL);

SQL> INSERT INTO emp (empno, ename)
  2  SELECT 10, ename
  3  FROM emp
  4  WHERE empno = 1;

1 row created.

SQL>
SQL> select * from emp;

     EMPNO ENAME      JOB              SAL     DEPTNO
---------- ---------- --------- ---------- ----------
         1 Kyle
        10 Kyle

SQL>
Home »
Oracle »
Table » 

Insert:
  1. Adding Rows Using the INSERT Statement
  2. Omitting the Column List
  3. Specifying a Null Value for a Column
  4. Single and Double Quotes and INSERT
  5. Copying Rows from One Table to Another with INSERT
Related: