CODEWITHSUNDEEP
databaseoracle10g
Nitish
Nitish

Reputation: 856

Regarding multiple insertion of rows through single query.(oracle)

Is the mechanism used in multiple insertion of rows in the table through single query is same as that of inserting single row with a single query.If not what is the exact mechanism?

Upvotes: 1

Views: 525

Answers (2)

Sathyajith Bhat
Sathyajith Bhat

Reputation: 21851

If you are fetching the data from an existing table, you can use INSERT INTO TABLE3 ( SELECT * FROM TABLE1 UNION SELECT * FROM TABLE2 ....) to fetch and insert the data in a single go.

SQL> SELECT * FROM SCOTT.EMP WHERE JOB = 'ANALYST1';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO GENDER
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- --------
      8909 LUTHAR     ANALYST1        7698 22-JUL-99       1232        788         50 F
      8999 AMAN       ANALYST1        7698 22-JUL-99       8569        788         50 M
      7788 SCOTT      ANALYST1        7566 19-APR-87       3000        150            M
      7902 2          ANALYST1        7566 03-DEC-81       3000                       M


SQL> SELECT * FROM SCOTT.EMP WHERE JOB = 'MANAGER';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO GENDER
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------
      7566 5          MANAGER         7839 02-APR-81       2975        150            F
      7698 10000      MANAGER         7839 01-MAY-81       2850        150            F
      7782 CLARK      MANAGER         7839 09-JUN-81       2450        150            F

SQL> CREATE TABLE EMP1 AS ( SELECT * FROM SCOTT.EMP WHERE 1=2);

Table created.

SQL> SELECT * FROM EMP1;

no rows selected

SQL> INSERT INTO EMP1 ( SELECT * FROM SCOTT.EMP WHERE JOB = 'ANALYST1'
  2  UNION
  3  SELECT * FROM SCOTT.EMP WHERE JOB = 'MANAGER');

7 rows created.

SQL> SELECT * FROM EMP1;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO GENDER
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- --------
      7566 5          MANAGER         7839 02-APR-81       2975        150            F
      7698 10000      MANAGER         7839 01-MAY-81       2850        150            F
      7782 CLARK      MANAGER         7839 09-JUN-81       2450        150            F
      7788 SCOTT      ANALYST1        7566 19-APR-87       3000        150            M
      7902 2          ANALYST1        7566 03-DEC-81       3000                       M
      8909 LUTHAR     ANALYST1        7698 22-JUL-99       1232        788         50 F
      8999 AMAN       ANALYST1        7698 22-JUL-99       8569        788         50 M

7 rows selected.

Upvotes: 2

Jon
Jon

Reputation: 36

You can chain many SQL statements in a single Oracle query by separating each SQL command with a semicolon. For instance: INSERT INTO table VALUES(1,2); INSERT INTO table VALUES(3,4);

Upvotes: 0

Related Questions