Oracle Insert Statement
Published by : Obay Salah , December 1, 2024
In Oracle, INSERT statement is used to add a single record or multiple records into the table.
Syntax: (Inserting a single record using the Values keyword):
INSERT INTO table (column1, column2, ... column_n ) VALUES (expression1, expression2, ... expression_n );
Syntax: (Inserting multiple records using a SELECT statement):
INSERT INTO table (column1, column2, ... column_n ) SELECT expression1, expression2, ... expression_n FROM source_table WHERE conditions;
Parameters:
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.
Oracle Insert Example: By VALUE keyword
It is the simplest way to insert elements to a database by using VALUE keyword.
See this example:
Consider here the already created suppliers table. Add a new row where the value of supplier_id is 23 and supplier_name is Flipkart.
See this example:
INSERT INTO suppliers (supplier_id, supplier_name) VALUES (50, 'Flipkart');
Output:
1 row(s) inserted. 0.02 seconds
Oracle Insert Example: By SELECT statement
This method is used for more complicated cases of insertion. In this method insertion is done by SELECT statement. This method is used to insert multiple elements.
See this example:
In this method, we insert values to the "suppliers" table from "customers" table. Both tables are already created with their respective columns.
Execute this query:
INSERT INTO suppliers (supplier_id, supplier_name) SELECT age, address FROM customers WHERE age > 20;
Output:
4 row(s) inserted. 0.00 seconds
You can even check the number of rows that you want to insert by following statement:
SELECT count(*) FROM customers WHERE age > 20;
Output:
Count(*) 4
Comments
no comment yet!