1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

INSERT Statements in SQL

Discussion in 'SQL PL/SQL' started by rajavu, Nov 10, 2008.

  1. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    INSERT is the one of the most widely used DML Statements in Oracle (or in any Database). There are different ways of inserting into One table in Oracle. But some of the methods are not yet fully explored. Hope this article will be useful for the SQL Developers especially for the newbies.

    Lets describe the tables used first ..

    Code (Text):

    SQL> Desc EMP ;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------

     EMPNO                                     NOT NULL NUMBER(4)
     ENAME                                              VARCHAR2(10)
     JOB                                                VARCHAR2(9)
     MGR                                                NUMBER(4)
     HIREDATE                                           DATE
     SAL                                                NUMBER(7,2)
     COMM                                               NUMBER(7,2)
     DEPTNO                                             NUMBER(2)

    SQL> Desc EMP_10;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------

     EMPNO                                              NUMBER(4)
     ENAME                                              VARCHAR2(10)
     JOB                                                VARCHAR2(9)
     MGR                                                NUMBER(4)
     SAL                                                NUMBER(7,2)

    SQL> Desc EMP_20;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------

     EMPNO                                              NUMBER(4)
     ENAME                                              VARCHAR2(10)
     JOB                                                VARCHAR2(9)
     MGR                                                NUMBER(4)
     SAL                                                NUMBER(7,2)

    SQL> Desc EMP_30;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------

     EMPNO                                              NUMBER(4)
     ENAME                                              VARCHAR2(10)
     JOB                                                VARCHAR2(9)
     MGR                                                NUMBER(4)
     SAL                                                NUMBER(7,2)

    SQL> Desc EMP_XX;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------

     EMPNO                                              NUMBER(4)
     ENAME                                              VARCHAR2(10)
     JOB                                                VARCHAR2(9)
     MGR                                                NUMBER(4)
     SAL                                                NUMBER(7,2)

    SQL> Desc STATE_COUNTRY ;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------

     STATE_CODE                                         VARCHAR2(3)
     STATE_NAME                                         VARCHAR2(20)
     COUNTRY_CODE                                       VARCHAR2(3)

    SQL> Desc STATE_COUNTRY_BKP ;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------

     STATE_CODE                                         VARCHAR2(3)
     STATE_NAME                                         VARCHAR2(20)
     COUNTRY_CODE                                       VARCHAR2(3)

    SQL> Desc EMP_MGR;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------

     EMPNO                                              NUMBER(4)
     ENAME                                              VARCHAR2(10)
     JOB                                                VARCHAR2(9)
     MGR                                                NUMBER(4)
     HIREDATE                                           DATE
     SAL                                                NUMBER(7,2)
     COMM                                               NUMBER(7,2)
     DEPTNO                                             NUMBER(2)

    SQL>

     

    Insert with Column list

    This is the most popular method of insert statement . This statement lists out the column names while inserting the table. This is used especially when only limited columns are populated or they are populated with fluctuating order.

    Code (Text):

    SQL> INSERT INTO STATE_COUNTRY (STATE_CODE ,STATE_NAME)
      2         VALUES ('KER', 'KERALA');

    1 row created.

    SQL> INSERT INTO STATE_COUNTRY (STATE_NAME,STATE_CODE )
      2         VALUES ('KARNATAKA', 'KAR');

    1 row created.

    SQL> INSERT INTO STATE_COUNTRY (STATE_CODE ,STATE_NAME,COUNTRY_CODE)
      2         VALUES ('PJB', 'PANJAB','IND');

    1 row created.

    SQL> SELECT * FROM STATE_COUNTRY;

    STA STATE_NAME           COU
    --- -------------------- ---
    KER KERALA               IND
    KAR KARNATAKA            IND
    PJB PANJAB               IND

    SQL>

     
    Insert without Column list

    Here rows are populated with same order of columns , hence no need for listing out the columns . It will give erroer , if the order is changed or all columns are not referred .

    Code (Text):

    SQL> INSERT INTO STATE_COUNTRY
      2         VALUES ('MHR', 'MAHARASHTRA');
    INSERT INTO STATE_COUNTRY
                *
    ERROR at line 1:
    ORA-00947: not enough values


    SQL> INSERT INTO STATE_COUNTRY
      2         VALUES ('MHR', 'MAHARASHTRA','IND');

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> SELECT * FROM STATE_COUNTRY;

    STA STATE_NAME           COU
    --- -------------------- ---
    KER KERALA               IND
    KAR KARNATAKA            IND
    PJB PANJAB               IND
    MHR MAHARASHTRA          IND

    SQL> Desc EMP_MGR;
     
    Insert with SELECT statement

    It is used to populate one or more records into the target table from Source table using SELECT Statement . Here It is needed to have the same or convertible datatypes .

    Code (Text):

    SQL> SELECT COUNT(*) FROM EMP_MGR;

      COUNT(*)
    ----------
             0

    SQL> INSERT INTO EMP_MGR
      2  SELECT * FROM EMP;

    14 rows created.

    SQL> SELECT COUNT(*) FROM EMP_MGR;

      COUNT(*)
    ----------
            14

    SQL>

     

    INSERT WHEN

    You can use WHEN condition to populate the tables along with INSERT command. WHEN condition is used to insert the records conditionally .

    Code (Text):

    SQL> TRUNCATE TABLE EMP_10;

    Table truncated.

    SQL> TRUNCATE TABLE EMP_20;

    Table truncated.

    SQL> TRUNCATE TABLE EMP_30;

    Table truncated.

    SQL> TRUNCATE TABLE EMP_XX;

    Table truncated.

    SQL> INSERT
      2  WHEN (DEPTNO=10) THEN
      3    INTO EMP_10 (EMPNO,ENAME,JOB,MGR,SAL)
      4    VALUES (EMPNO,ENAME,JOB,MGR,SAL)
      5  WHEN (DEPTNO=20) THEN
      6    INTO EMP_20 (EMPNO,ENAME,JOB,MGR,SAL)
      7    VALUES (EMPNO,ENAME,JOB,MGR,SAL)
      8  WHEN (DEPTNO=30) THEN
      9    INTO EMP_30 (EMPNO,ENAME,JOB,MGR,SAL)
     10    VALUES (EMPNO,ENAME,JOB,MGR,SAL)
     11  ELSE
     12    INTO EMP_XX (EMPNO,ENAME,JOB,MGR,SAL)
     13    VALUES (EMPNO,ENAME,JOB,MGR,SAL)
     14  SELECT * FROM EMP;

    14 rows created.

    SQL> SELECT * FROM EMP_10;

         EMPNO ENAME      JOB              MGR        SAL
    ---------- ---------- --------- ---------- ----------
          7782 CLARK      MANAGER         7839       2450
          7839 KING       PRESIDENT                  5000
          7934 MILLER     CLERK           7782       1300

    SQL> SELECT * FROM EMP_20;

         EMPNO ENAME      JOB              MGR        SAL
    ---------- ---------- --------- ---------- ----------
          7369 SMITH      CLERK           7902        800
          7566 JONES      MANAGER         7839       2975
          7788 SCOTT      ANALYST         7566       3000
          7876 ADAMS      CLERK           7788       1100
          7902 FORD       ANALYST         7566       3000

    SQL> SELECT * FROM EMP_30;

         EMPNO ENAME      JOB              MGR        SAL
    ---------- ---------- --------- ---------- ----------
          7499 ALLEN      SALESMAN        7698       1600
          7521 WARD       SALESMAN        7698       1250
          7654 MARTIN     SALESMAN        7698       1250
          7698 BLAKE      MANAGER         7839       2850
          7844 TURNER     SALESMAN        7698       1500
          7900 JAMES      CLERK           7698        950

    6 rows selected.

    SQL> SELECT * FROM EMP_XX;

    no rows selected

    SQL>

     
    INSERT ALL

    INSERT ALL is used to populate multiple records into one or more target tables. It merges multiple insert statements into single INSERT statement.

    Code (Text):

    SQL> INSERT ALL
      2  INTO STATE_COUNTRY (STATE_CODE ,STATE_NAME)
      3              VALUES ('WBG', 'WEST BENGAL')
      4  INTO STATE_COUNTRY (STATE_CODE ,STATE_NAME)
      5              VALUES ('ORS', 'ORISSA')
      6  INTO STATE_COUNTRY (STATE_CODE ,STATE_NAME)
      7              VALUES ('JKS', 'JAMMU KASHMIR')
      8  INTO STATE_COUNTRY_BKP (STATE_CODE ,STATE_NAME)
      9              VALUES ('WBG', 'WEST BENGAL')
     10  INTO STATE_COUNTRY_BKP (STATE_CODE ,STATE_NAME)
     11              VALUES ('ORS', 'ORISSA')
     12  INTO STATE_COUNTRY_BKP (STATE_CODE ,STATE_NAME)
     13              VALUES ('JKS', 'JAMMU KASHMIR')
     14  SELECT * FROM DUAL;

    6 rows created.

    SQL> SELECT * FROM STATE_COUNTRY;

    STA STATE_NAME           COU
    --- -------------------- ---
    KER KERALA               IND
    KAR KARNATAKA            IND
    PJB PANJAB               IND
    MHR MAHARASHTRA          IND
    WBG WEST BENGAL          IND
    ORS ORISSA               IND
    JKS JAMMU KASHMIR        IND

    7 rows selected.

    SQL> SELECT * FROM STATE_COUNTRY_BKP;

    STA STATE_NAME           COU
    --- -------------------- ---
    WBG WEST BENGAL
    ORS ORISSA
    JKS JAMMU KASHMIR

    SQL> commit;

    Commit complete.

    SQL>
     
    INSERT ALL ... WHEN

    WHEN clause can also used along with INSERT ALL for conditional population into multiple tables.

    Code (Text):

    SQL> TRUNCATE TABLE EMP_10;

    Table truncated.

    SQL> TRUNCATE TABLE EMP_20;

    Table truncated.

    SQL> TRUNCATE TABLE EMP_30;

    Table truncated.

    SQL> TRUNCATE TABLE EMP_XX;

    Table truncated.

    SQL>
    SQL> INSERT ALL
      2  WHEN (DEPTNO=10) THEN
      3    INTO EMP_10 (EMPNO,ENAME,JOB,MGR,SAL)
      4    VALUES (EMPNO,ENAME,JOB,MGR,SAL)
      5  WHEN (DEPTNO=20) THEN
      6    INTO EMP_20 (EMPNO,ENAME,JOB,MGR,SAL)
      7    VALUES (EMPNO,ENAME,JOB,MGR,SAL)
      8  WHEN (DEPTNO=30) THEN
      9    INTO EMP_30 (EMPNO,ENAME,JOB,MGR,SAL)
     10    VALUES (EMPNO,ENAME,JOB,MGR,SAL)
     11  ELSE
     12    INTO EMP_XX (EMPNO,ENAME,JOB,MGR,SAL)
     13    VALUES (EMPNO,ENAME,JOB,MGR,SAL)
     14  SELECT * FROM EMP;

    14 rows created.

    SQL>
    SQL> SELECT * FROM EMP_10;

         EMPNO ENAME      JOB              MGR        SAL
    ---------- ---------- --------- ---------- ----------
          7782 CLARK      MANAGER         7839       2450
          7839 KING       PRESIDENT                  5000
          7934 MILLER     CLERK           7782       1300

    SQL> SELECT * FROM EMP_20;

         EMPNO ENAME      JOB              MGR        SAL
    ---------- ---------- --------- ---------- ----------
          7369 SMITH      CLERK           7902        800
          7566 JONES      MANAGER         7839       2975
          7788 SCOTT      ANALYST         7566       3000
          7876 ADAMS      CLERK           7788       1100
          7902 FORD       ANALYST         7566       3000

    SQL> SELECT * FROM EMP_30;

         EMPNO ENAME      JOB              MGR        SAL
    ---------- ---------- --------- ---------- ----------
          7499 ALLEN      SALESMAN        7698       1600
          7521 WARD       SALESMAN        7698       1250
          7654 MARTIN     SALESMAN        7698       1250
          7698 BLAKE      MANAGER         7839       2850
          7844 TURNER     SALESMAN        7698       1500
          7900 JAMES      CLERK           7698        950

    6 rows selected.

    SQL> SELECT * FROM EMP_XX;

    no rows selected

    SQL>

     
    INSERT FIRST ... WHEN

    Here WHEN clause is evaluated in the order in which it appears in the statement. For the first WHEN clause that evaluates to true, the database executes the corresponding INTO clause and skips subsequent WHEN clauses for the given row else goes to the next WHEN condition and so on.

    Code (Text):

    SQL> TRUNCATE TABLE EMP_10;

    Table truncated.

    SQL> TRUNCATE TABLE EMP_20;

    Table truncated.

    SQL> TRUNCATE TABLE EMP_30;

    Table truncated.

    SQL> TRUNCATE TABLE EMP_XX;

    Table truncated.

    SQL>
    SQL> INSERT FIRST
      2  WHEN (DEPTNO<20) THEN
      3    INTO EMP_10 (EMPNO,ENAME,JOB,MGR,SAL)
      4    VALUES (EMPNO,ENAME,JOB,MGR,SAL)
      5  WHEN (DEPTNO<30) THEN
      6    INTO EMP_20 (EMPNO,ENAME,JOB,MGR,SAL)
      7    VALUES (EMPNO,ENAME,JOB,MGR,SAL)
      8  WHEN (DEPTNO<40) THEN
      9    INTO EMP_30 (EMPNO,ENAME,JOB,MGR,SAL)
     10    VALUES (EMPNO,ENAME,JOB,MGR,SAL)
     11  ELSE
     12    INTO EMP_XX (EMPNO,ENAME,JOB,MGR,SAL)
     13    VALUES (EMPNO,ENAME,JOB,MGR,SAL)
     14  SELECT * FROM EMP;

    14 rows created.

    SQL>
    SQL> SELECT * FROM EMP_10;

         EMPNO ENAME      JOB              MGR        SAL
    ---------- ---------- --------- ---------- ----------
          7782 CLARK      MANAGER         7839       2450
          7839 KING       PRESIDENT                  5000
          7934 MILLER     CLERK           7782       1300

    SQL> SELECT * FROM EMP_20;

         EMPNO ENAME      JOB              MGR        SAL
    ---------- ---------- --------- ---------- ----------
          7369 SMITH      CLERK           7902        800
          7566 JONES      MANAGER         7839       2975
          7788 SCOTT      ANALYST         7566       3000
          7876 ADAMS      CLERK           7788       1100
          7902 FORD       ANALYST         7566       3000

    SQL> SELECT * FROM EMP_30;

         EMPNO ENAME      JOB              MGR        SAL
    ---------- ---------- --------- ---------- ----------
          7499 ALLEN      SALESMAN        7698       1600
          7521 WARD       SALESMAN        7698       1250
          7654 MARTIN     SALESMAN        7698       1250
          7698 BLAKE      MANAGER         7839       2850
          7844 TURNER     SALESMAN        7698       1500
          7900 JAMES      CLERK           7698        950

    6 rows selected.

    SQL> SELECT * FROM EMP_XX;

    no rows selected

    SQL>
     
    All of the above SQL syntax will apply to the PL/SQL also. Moreover there are some special cases exclusively applicable for the PL/SQL.

    Insert using Record

    In PL/SQL table can be populated using the Records where the column values are sotored .

    Code (Text):

    SQL> TRUNCATE TABLE EMP_MGR;

    Table truncated.

    SQL> DECLARE
      2   EMPLOYEE  EMP%ROWTYPE;
      3  BEGIN
      4    FOR R IN ( SELECT * FROM EMP )
      5    LOOP
      6
      7    EMPLOYEE := R ;
      8
      9    INSERT INTO EMP_MGR
     10    VALUES EMPLOYEE;
     11
     12    END LOOP;
     13
     14    COMMIT;
     15  END;
     16  /

    PL/SQL procedure successfully completed.

    SQL> SELECT COUNT(*) FROM EMP_MGR;

      COUNT(*)
    ----------
            14

    SQL>
     
    Insert with RETURNING clause

    RETURNING Clause along with the INSERT returns the inserted value to a variable or collection . and this can be processed for further use. This avoids the another select statement after Insert and fetches the record with the same insert statement.

    Code (Text):

    SQL> TRUNCATE TABLE EMP_MGR;

    Table truncated.

    SQL> SET SERVEROUT ON
    SQL> DECLARE
      2   EMPLOYEE  EMP%ROWTYPE;
      3   ENAME_v   VARCHAR2(25);
      4  BEGIN
      5    FOR R IN ( SELECT * FROM EMP )
      6    LOOP
      7
      8    EMPLOYEE := R ;
      9
     10    INSERT INTO EMP_MGR
     11    VALUES EMPLOYEE
     12    returning ENAME
     13    into ENAME_V;
     14
     15    DBMS_OUTPUT.PUT_LINE (ENAME_V);
     16
     17    END LOOP;
     18
     19    COMMIT;
     20  END;
     21  /
    SMITH
    ALLEN
    WARD
    JONES
    MARTIN
    BLAKE
    CLARK
    SCOTT
    KING
    TURNER
    ADAMS
    JAMES
    FORD
    MILLER

    PL/SQL procedure successfully completed.

    SQL> SELECT COUNT(*) FROM EMP_MGR;

      COUNT(*)
    ----------
            14

    SQL>
     
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    hi raj, one point i want know that is , in insert all and insert first statements
    i) if we are writing "select * from emp", if emp table consists of 14 records, one record will be inserted for 14 times. why it happens.
    "select * from emp where empno = 7788" works fine. here it satisfies only for record , and the record will be inserted for once. and
    ii)why we have to write the "select" in the statement