Oracle SQL - INSERT Using Subqueries

Introduction

The following code creates a table that we'll insert some new data into.

It shows shows the query that we'll use to generate the data that we'll be inserting.

Demo

SQL>
SQL>-- ww  w.  j  a v  a  2  s. com
SQL> CREATE TABLE dept_emp_names  -- create a table to populate
  2  ( deptname  VARCHAR2(10),
  3    location  VARCHAR2(8),
  4    empname   VARCHAR2(8),
  5    job       VARCHAR2(8)
  6  );
CREATE TABLE dept_emp_names  -- create a table to populate
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL>
SQL> SELECT d.dname, d.location, e.ename, e.job
  2  FROM  departments d, emp e
  3  WHERE e.deptno = d.deptno;

DNAME      | LOCATION | ENAME    | JOB
---------- | -------- | -------- | --------
TRAINING   | DALLAS   | SMITH    | TRAINER
SALES      | CHICAGO  | ALLEN    | SALESREP
ACCOUNTING | NEW YORK | WARD     | SALESREP
TRAINING   | DALLAS   | JACK     | MANAGER
SALES      | CHICAGO  | BROWN    | SALESREP
ACCOUNTING | NEW YORK | BLAKE    | MANAGER
ACCOUNTING | NEW YORK | CLARK    | MANAGER
TRAINING   | DALLAS   | SCOTT    | TRAINER
ACCOUNTING | NEW YORK | KING     | DIRECTOR
SALES      | CHICAGO  | BREAD    | SALESREP
TRAINING   | DALLAS   | ADAMS    | TRAINER

DNAME      | LOCATION | ENAME    | JOB
---------- | -------- | -------- | --------
SALES      | CHICAGO  | JONES    | ADMIN
TRAINING   | DALLAS   | FORD     | TRAINER
ACCOUNTING | NEW YORK | MARY     | ADMIN

14 rows selected.

SQL>
SQL> INSERT INTO dept_emp_names         -- Example 1
  2    ( deptname, location, empname, job)
  3    ( SELECT d.dname, d.location, e.ename, e.job
  4      FROM  departments d, emp e
  5      WHERE e.deptno = d.deptno
  6    );

14 rows created.

SQL>
SQL> SELECT *
  2  FROM dept_emp_names;

DEPTNAME   | LOCATION | EMPNAME  | JOB
---------- | -------- | -------- | --------
TRAINING   | DALLAS   | SMITH    | TRAINER
SALES      | CHICAGO  | ALLEN    | SALESREP
ACCOUNTING | NEW YORK | WARD     | SALESREP
TRAINING   | DALLAS   | JACK     | MANAGER
SALES      | CHICAGO  | BROWN    | SALESREP
ACCOUNTING | NEW YORK | BLAKE    | MANAGER
ACCOUNTING | NEW YORK | CLARK    | MANAGER
TRAINING   | DALLAS   | SCOTT    | TRAINER
ACCOUNTING | NEW YORK | KING     | DIRECTOR
SALES      | CHICAGO  | BREAD    | SALESREP
TRAINING   | DALLAS   | ADAMS    | TRAINER

DEPTNAME   | LOCATION | EMPNAME  | JOB
---------- | -------- | -------- | --------
SALES      | CHICAGO  | JONES    | ADMIN
TRAINING   | DALLAS   | FORD     | TRAINER
ACCOUNTING | NEW YORK | MARY     | ADMIN
TRAINING   | DALLAS   | SMITH    | TRAINER
SALES      | CHICAGO  | ALLEN    | SALESREP
ACCOUNTING | NEW YORK | WARD     | SALESREP
TRAINING   | DALLAS   | JACK     | MANAGER
SALES      | CHICAGO  | BROWN    | SALESREP
ACCOUNTING | NEW YORK | BLAKE    | MANAGER
ACCOUNTING | NEW YORK | CLARK    | MANAGER
TRAINING   | DALLAS   | SCOTT    | TRAINER

DEPTNAME   | LOCATION | EMPNAME  | JOB
---------- | -------- | -------- | --------
ACCOUNTING | NEW YORK | KING     | DIRECTOR
SALES      | CHICAGO  | BREAD    | SALESREP
TRAINING   | DALLAS   | ADAMS    | TRAINER
SALES      | CHICAGO  | JONES    | ADMIN
TRAINING   | DALLAS   | FORD     | TRAINER
ACCOUNTING | NEW YORK | MARY     | ADMIN
TRAINING   | DALLAS   | SMITH    | TRAINER
SALES      | CHICAGO  | ALLEN    | SALESREP
ACCOUNTING | NEW YORK | WARD     | SALESREP
TRAINING   | DALLAS   | JACK     | MANAGER
SALES      | CHICAGO  | BROWN    | SALESREP

DEPTNAME   | LOCATION | EMPNAME  | JOB
---------- | -------- | -------- | --------
ACCOUNTING | NEW YORK | BLAKE    | MANAGER
ACCOUNTING | NEW YORK | CLARK    | MANAGER
TRAINING   | DALLAS   | SCOTT    | TRAINER
ACCOUNTING | NEW YORK | KING     | DIRECTOR
SALES      | CHICAGO  | BREAD    | SALESREP
TRAINING   | DALLAS   | ADAMS    | TRAINER
SALES      | CHICAGO  | JONES    | ADMIN
TRAINING   | DALLAS   | FORD     | TRAINER
ACCOUNTING | NEW YORK | MARY     | ADMIN

42 rows selected.

SQL>
SQL> INSERT INTO dept_emp_names - Example 2
  2  (SELECT *
  3   FROM dept_emp_names
  4  );
INSERT INTO dept_emp_names - Example 2
                           *
ERROR at line 1:
ORA-00926: missing VALUES keyword


SQL>
SQL> COMMIT;

Commit complete.

SQL>
SQL> SELECT COUNT(1)
  2  FROM dept_emp_names;

 COUNT(1)
---------
 00042.00

SQL>

Related Topic