Useful information about INSERT statement in Oracle

Last updated on October 30th, 2018 at 04:56 am

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

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 : are 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 column, 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 multiple elements

  • 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 date function in the table

Suppose we have a date datatype column in the table, then we can use sysdate function to enter 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 specific date, we can used the standard fromat for 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 different format with some time , we can use 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;

Related Articles

Update statement in oracle

Delete from table statement in Oracle

How to Alter Table in Oracle

How to alter table add column oracle

Oracle Create table Syntax,Tip and Examples

Leave a Reply