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.
SQL> SQL>-- w ww .j a v a2 s . c o m 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>