SQL Insert Statement

Insert Statement

INSERT statement:-This is used for inserting new records into table. Below are the ways to insert new record
    1) INSERT INTO Statement
    2) INSERT ALL Statement
    3) Multiple Row INSERT into TABLE Statement (By reference of column values)
    4) INSERT Data only selected COLUMNS
    5) INSERT INTO SELECT Statement (Insert data from another table)

Below table is created for understanding examples :-

create table student (id number,name varchar2(20),dob date,course varchar2(10));

1) INSERT INTO statement:-Below is the syntax for same.
Syntax :-INSERT INTO <table_name> VALUES (value1, value2, value3, ...);

Note:-When inserting data into all columns of table then no need to specify the column names.
      Values should be in sequence of columns

Example:-

SQL> INSERT INTO STUDENT VALUES (101, 'Suresh', '12-JUL-1993', 'MCA');

1 row created.

If you don't know the sequence of columns the you can use below synatx
Syntax :-INSERT INTO <table_name>(column_name1, column_name2, ...) VALUES (value1, value2, ...);

Note:- In above syntax you have to put values on sequence what you have mentioned column name before "values" clause

Example:-

SQL> INSERT INTO STUDENT (id,name,dob,course) VALUES (101, 'Suresh', '12-JUL-1993', 'MCA');

1 row created.
 
2) INSERT ALL statement:-INSERT ALL statement is used to insert more then one records into table by one statement.
Syntax :-

INSERT ALL
    INTO <table_name>  (column1, column2, ...)  VALUES (value1, value2, ...)
    INTO <table_name>  (column1, column2, ...)  VALUES (value1, value2, ...)
    INTO <table_name>  (column1, column2, ...)  VALUES (value1, value2, ...)
    ....
    SELECT * FROM dual;
   
Example :-

SQL> INSERT ALL
  INTO STUDENT (id,name,dob,course) VALUES (101, 'Suresh', '12-JUL-1993', 'MCA')
  INTO STUDENT (id,name,dob,course) VALUES (102, 'Mahesh', '18-AUG-1991', 'BCA')
  INTO STUDENT (id,name,dob,course) VALUES (103, 'Dinesh', '23-SEP-1990', 'BCA')
  INTO STUDENT (id,name,dob,course) VALUES (104, 'Ganesh', '25-APR-1993', 'MCA')
SELECT * FROM dual;

4 rows created.

3) SQL Multiple Row Insert into Table Statements :-By taking reference for column values, we can INSERT multiple rows. "&" sign is used to take a value from keyboard with column name.
 
Note:-If you want to add another record you just execute forward slash (/) on SQL*PLUS to again execute last statement automatically and you can insert new data again.

Note :-In SQL*PLUS Forward Slash "/" is used to execute last statement.

Syntax:-
SQL> INSERT INTO STUDENT (id,name,dob,course) VALUES (&id,'&name','&dob','&course');
Enter value for id: 101
Enter value for name: Ganesh
Enter value for dob: 12-AUG-1993
Enter value for course: MCA
old   1: INSERT INTO STUDENT (id,name,dob,course) VALUES (&id,'&name','&dob','&course')
new   1: INSERT INTO STUDENT (id,name,dob,course) VALUES (101,'Ganesh','12-AUG-1993','MCA')

1 row created.

SQL> /
Enter value for id: 102
Enter value for name: Mahesh
Enter value for dob: 18-APR-1990
Enter value for course: BCA
old   1: INSERT INTO STUDENT (id,name,dob,course) VALUES (&id,'&name','&dob','&course')
new   1: INSERT INTO STUDENT (id,name,dob,course) VALUES (102,'Mahesh','18-APR-1990','BCA')

1 row created.

SQL>

4) Insert Data in selected COLUMNS :-By this way, we can insert data into selected columns only. below is the syntax

Syntax:-INSERT INTO <Table_Name> (column_name1, ...) VALUES (value1,...);

Note:-All null-able column you can left. but all mandatory columns have to take on insert statement

Example:-

SQL> INSERT INTO STUDENT (id,name,course) VALUES (109, 'Gyanesh', 'MCA');

1 row created.


5) INSERT INTO SELECT Statement:-By this way, we can insert data into table which is selected from another table.

Syntax:- INSERT INTO <table_name> (column_name1,column_name2,...)
    SELECT column_name1, column_name1 ... FROM <another_table_name>
    WHERE condition;

Example:-

SQL> create table student1 (id number,name varchar2(20),dob date,course varchar2(10),address varchar2(50));

Table created.

SQL> insert into student1 (id,name,dob,course) select id,name,dob,course from student;

2 rows created.