Article Sections
Categories
Recent Article Comments
Currently Active UsersLets describe the tables used first ..
Code :
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 :
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 :
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 :
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 :
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 :
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 :
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 :
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 :
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 :
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>
1 Comment
Rate this article