INSERT statement in Oracle

INSERT statement in oracle is a DML ( Data Manipulation Language) command. It is used to insert new rows into the oracle tables in the oracle database

Syntax
INSERT INTO <table_name>
(col1, col2, ... column_n )
VALUES
(value1, value2, ... value_n );

Here
table_name: Table where new records need to be inserted
col1,col2: is the column in the tables
value1,value2….: are the values corresponding to col1,col2 …in the table

Only one row is inserted with this syntax in the table

Suppose you want to enter records in the EMP table, then

insert into emp (emp_id, emp_name,dept_id,first_name,last_name,salary)
values ('101','JOHN', 10,'JOHN' ,'ROGER', 3000);

Important Note

• It is not necessary to give column names in-case you are specifying the values in the correct order and value for each column in the table is provided
• It is recommended to put single quotation marks around character values.

How to insert Null values into table

In case we are putting null values for the few columns, we can omit the columns in the insert command

insert into emp (emp_id, emp_name,dept_id,salary)
values ('101','JOHN', 10, 3000);

Insert using Select statement

We can insert into the table using select statement also

INSERT INTO <table_name>
(col1, col2, ... column_n )
select col1, col2, ... column_n from < table_name2>

This method can be used to insert into multiple rows

• We do not need to use values clause here
• We need to match the column in the insert to the select query to make it work
• It is not necessary to give column names in-case you are specifying the values in the correct order and value for each column in the table is provided

Example
insert into emp_master (emp_id ,salary) 
select emp_id ,salary from emp;

INSERT INTO emp_master (emp_id ,salary) 
  WITH sal AS ( 
    SELECT 201,5000    FROM dual UNION ALL 
    SELECT 201, 10000   FROM dual UNION ALL 
    SELECT 204, 10004 FROM dual UNION ALL 
    SELECT 7, 5675476    FROM dual 
  ) 
  SELECT * FROM sal;

 

Insert date function in the table

Suppose we have an oracle date datatype column in the table, then we can use the sysdate function to enter the date in the insert statement

insert into emp (emp_id,emp_name,salary,hire_date) values 
( '10','JOHN',1000, sysdate);

insert into emp (emp_id,emp_name,salary,hire_date) values 
( '10','JOHN',1000, sysdate -1);

Or if we need to need to specify the specific date, we can use the standard format for the date ‘DD-MON-YYYY’.With this time default to midnight

insert into emp (emp_id,emp_name,salary,hire_date) values 
( '10','JOHN',1000, '18-AUG-2018');

or if we need to enter data in a different format with some time, we can use the to_date function

insert into emp (emp_id,emp_name,salary,hire_date) values 
( '10','JOHN',1000, TO_DATE('2018/08/18 01:02:44', 'yyyy/mm/dd hh24:mi:ss'));

Oracle insert all statement

Oracle insert all statement helps us add multiple rows in the table in one go , or we can add  multiple rows in different tables with the same insert statement

Syntax for One table

INSERT ALL INTO
<table_name> (col1, col2, ... column_n ) VALUES (value1, value2, ... value_n )
<table_name> (col1, col2, ... column_n ) VALUES (value1, value2, ... value_n )
<table_name> (col1, col2, ... column_n ) VALUES (value1, value2, ... value_n )
SELECT * FROM dual;

Syntax for Multiple tables

INSERT ALL INTO
<table_name1> (col1, col2, ... column_n ) VALUES (value1, value2, ... value_n )
<table_name2> (col1, col2, ... column_n ) VALUES (value1, value2, ... value_n )
<table_name3> (col1, col2, ... column_n ) VALUES (value1, value2, ... value_n )
SELECT * FROM dual;

This is equivalent to

INSERT  INTO <table_name> (col1, col2, ... column_n ) VALUES (value1, value2, ... value_n );
INSERT  INTO <table_name> (col1, col2, ... column_n ) VALUES (value1, value2, ... value_n )
INSERT  INTO <table_name> (col1, col2, ... column_n ) VALUES (value1, value2, ... value_n )

Examples

insert all into
emp (emp_id, emp_name,dept_id,first_name,last_name,salary) values ('101','JOHN', 10,'JOHN' ,'ROGER', 2000)
emp (emp_id, emp_name,dept_id,first_name,last_name,salary) values ('102','SMITH', 10,'JOHN' ,'ROGER', 3000)
emp (emp_id, emp_name,dept_id,first_name,last_name,salary) values ('103','BILL', 10,'JOHN' ,'ROGER', 4000)
SELECT * FROM dual;

insert all into
emp (emp_id, emp_name,dept_id,first_name,last_name,salary) values ('101','JOHN', 10,'JOHN' ,'ROGER', 2000)
dept (dept_no, dept_name,dept_location) values ('20','BILLING', 'NEWYORK)
emp (emp_id, emp_name,dept_id,first_name,last_name,salary) values ('103','BILL', 10,'JOHN' ,'ROGER', 4000)
SELECT * FROM dual;

insert all statements can also be used in the below manner

insert all 
 INTO emp (emp_id, emp_name,dept_id,salary) values (id,name, dept_no, sal)
 INTO dept (dept_no, dept_name,dept_location) values (dept_no,dept_name,location)
 WITH names AS ( 
     SELECT 4 id, 'John' name,10 dept_no,'LABOUR' dept_name,'INDIA' location, 45000 sal    FROM dual UNION ALL 
     SELECT 5 id, 'Mac' name,20 dept_no,'FIRE' dept_name,'INDIA' location, 567000 sal   FROM dual UNION ALL 
     SELECT 6 id, 'Ruth' name,30 dept_no,'HEALTH' dept_name,'INDIA' location, 5600 sal FROM dual UNION ALL 
     SELECT 8 id, 'bill' name,40 dept_no,'WATER' dept_name,'INDIA' location, 158750 sal     FROM dual 
   ) 
   SELECT * FROM names ;

It is possible to use conditional statements in insert all statement

insert all 
 WHEN 1=1 THEN 
 INTO emp (emp_id, emp_name,dept_id,salary) values (id,name, dept_no, sal)
 WHEN dept_no IS NOT NULL THEN 
 dept (dept_no, dept_name,dept_location) values (dept_no,dept_name,location)
 WITH names AS ( 
     SELECT 4 id, 'John' name,10 dept_no,'LABOUR' dept_name,'INDIA' location, 45000 sal    FROM dual UNION ALL 
     SELECT 5 id, 'Mac' name,20 dept_no,'FIRE' dept_name,'INDIA' location, 567000 sal   FROM dual UNION ALL 
     SELECT 6 id, 'Ruth' name,30 dept_no,'HEALTH' dept_name,'INDIA' location, 5600 sal FROM dual UNION ALL 
     SELECT 8 id, 'bill' name,40 dept_no,'WATER' dept_name,'INDIA' location, 158750 sal     FROM dual 
     SELECT 9 id, 'July' name,NULL dept_no,NULL dept_name,NULL location, 158750 sal     FROM dual 
   ) 
   SELECT * FROM names ;

I hope you like this content on the insert statement in oracle. I have explained various ways in which inserts can be performed in the tables with ample examples.

Related Articles

Update statement in oracle : We use the Update statement in oracle to modify the existing rows in the oracle table in the oracle database. Update can be executed in multiple ways
Delete from table statement in Oracle: Delete from the table in oracle is used to delete the rows. DELETE  rows can be done using EXISTS/NOT EXISTS clause , table based on a subquery, cascade
How to Alter Table in Oracle : Alter table in oracle is used to modify a column, drop and add constraints, change the data type of the table column, change the table storage parameters
How to alter table add column oracle: Useful insight into How to alter table add column oracle. Details about the fast add column feature introduced in oracle 11g also given
Oracle documentation on insert