In Oracle, INSERT statement is used to add a single record or multiple records into the table.
Inserting a single record using the VALUES:
Syntax:
INSERT INTO table
(column1, column2, ... column_n )
VALUES
(expression1, expression2, ... expression_n );
Inserting multiple records using a SELECT statement):
Syntax:
INSERT INTO table
(column1, column2, ... column_n )
SELECT expression1, expression2, ... expression_n
FROM source_table
WHERE conditions;
1) table: The table to insert the records into.
2) column1, column2, ... column_n:
The columns in the table to insert values.
3) expression1, expression2, ... expression_n:
The values to assign to the columns in the table. So column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and so on.
4) source_table:
The source table when inserting data from another table.
5) conditions:
The conditions that must be met for the records to be inserted.
Example:
INSERT INTO students
(student_id, sname,dob,student_type,student_city)
VALUES
(2101, 'John',TO_DATE('12-06-1999', 'DD/MM/YYYY'),'Day Scholar','Gurgaon');
Output:
1 row(s) inserted. 0.02 seconds
INSERT ALL
The Oracle INSERT ALL statement is used to insert multiple rows with a single INSERT statement. You can insert the rows into one table or multiple tables by using only one SQL command.
Syntax:
INSERT ALL
INTO table_name (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
INTO table_name(column1, column2, column_n) VALUES (expr1, expr2, expr_n)
INTO table_name (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
SELECT * FROM dual;
Example:
INSERT ALL
INTO students (student_id, sname,dob,student_type,student_city) VALUES (2102,
'Kelvin',TO_DATE('12/07/1998', 'DD/MM/YYYY'),'Day Scholar','Noida')
INTO students (student_id, sname,dob,student_type,student_city) VALUES (2103,
'Peter',TO_DATE('28/02/2000', 'DD/MM/YYYY'),' Hostler','Pune')
INTO students (student_id, sname,dob,student_type,student_city) VALUES (2104,
'Stefin',TO_DATE('17/09/2001', 'DD/MM/YYYY'),'Day Scholar','Gurgaon')
SELECT * FROM dual;
Output;
3 row(s) inserted.
Thank you for reading.
Feel Free to ask you doubts in comments.