SQL INSERT Statement

INSERT Statement is used to add rows to a Table.

Syntax:-
INSERT INTO <TABLE_NAME>(COL1,COL2,...)
VALUES(VAL1,VAL2,...);

Different way to INSERT the data into TABLE
Already we created STUDENTS table so we will use this table for our INSERTION

CASE1:-
INSERT INTO STUDENTS
VALUES(11,'NANDAN','DAS','14-FEB-1988','01-JAN-2000',25000,'M','ADMIN');

In this case we need to provide all column values in original order.

CASE2:-
INSERT INTO STUDENTS(STUDNO,FNAME,DOJ,GENDER,INSERTBY)
VALUES(12,'SOURAV','01-JAN-2000','M','ADMIN');

In this case VALUES clause should follow order of INSERT list.
Remember if your table have any NOT NULL constraints then we need to pass the value for those column.

CASE3:-
INSERT INTO STUDENTS(STUDNO,FNAME,LNAME,DOJ,GENDER,INSERTBY)
VALUES(12,'SAMIR',NULL,'01-JAN-2000','M','');
In this case we are inserting NULL data by declaring NULL or omit the column from the list or defining '';

CASE4:-
INSERT INTO STUDENTS
SELECT * FROM STUDENTS_TAB1;

In this case if you want to copy the data from any other table(if your target and source table have
same structure, if you want to copy specific column then define the column in INSERT and
SELECT  both the place  ).

Multi table Inserts (by using INSERT ALL and INSERT FIRST)

INSERT ALL :-

Create 3 duplicate table of your STUDENTS table

Steps1:-

CREATE TABLE STUDENTS_DUP_TBL11

AS

SELECT * FROM STUDENTS WHERE 1=2 ;

CREATE TABLE STUDENTS_DUP_TBL12

AS

SELECT * FROM STUDENTS WHERE 1=2;

CREATE TABLE STUDENTS_DUP_TBL13

AS

SELECT * FROM STUDENTS WHERE 1=2;

Steps2:-

Check below table should not have any data

SELECT * FROM STUDENTS_DUP_TBL11;

SELECT * FROM STUDENTS_DUP_TBL12;

SELECT * FROM STUDENTS_DUP_TBL13;

Steps3:-

INSERT ALL

WHEN STUDNO <=11 THEN INTO STUDENTS_DUP_TBL11

WHEN STUDNO =12 THEN INTO STUDENTS_DUP_TBL12

WHEN (STUDNO >12 AND STUDNO<14) THEN INTO STUDENTS_DUP_TBL13

SELECT * FROM STUDENTS;

Steps2:-

Check below table should data

SELECT * FROM STUDENTS_DUP_TBL11;

SELECT * FROM STUDENTS_DUP_TBL12;

SELECT * FROM STUDENTS_DUP_TBL13;


INSERT FIRST :-
We can also use the INSERT FIRST clause, if we do, then the FIRST condition specified in
the statement that is true will be applied by the RDBMS and it will stop evaluating the
rest of the conditions.

INSERT FIRST
WHEN STUDNO <=11  THEN INTO STUDENTS_DUP_TBL11
WHEN STUDNO <=12 THEN INTO STUDENTS_DUP_TBL12
else INTO STUDENTS_DUP_TBL13
SELECT * FROM STUDENTS;