×
☰ See All Chapters

Oracle INSERT Statement

In Oracle, INSERT statement is used to insert a single record or multiple records into the table. After inserting record we need to commit using COMMIT statement to permanently save the data. We can use rollback command to undo the insert statement changes.

Data Is Case Sensitive in insert statement

Do not forget that SQL statements can be in uppercase or lowercase. However, data is always case-sensitive. For example, if you enter data into the database as uppercase, it must be referenced in uppercase.

Syntax to insert values to all the columns

  • Values should be passed to VALUES clause and values should be separated by comma. 

  • Number of values should be same as number of columns. 

  • All values should be inside quotation mark, Although single quotation marks are not required around numeric data that is being inserted, they may be used with any data type. Said another way, single quotation marks are optional when referring to numeric data values in the database, but they are required for all other data values (data types). Although usually a matter of preference, most SQL users choose not to use quotation marks with numeric values because it makes their queries more readable. 

INSERT INTO <table-name> VALUES (<value1>, <value2>, <value3>);

COMMIT;

Inserting NULL Values

We can insert a NULL value into a column of a table using the keyword NULL.

INSERT INTO <table-name> VALUES (<value1>, NULL, <value3>,NULL, NULL);

COMMIT;

Example to insert values to all the columns

CREATE TABLE EMPLOYEE(

        EMPLOYEE_ID NUMBER(5),

        NAME VARCHAR2(30),

        SALARY NUMBER(6)

);

 

INSERT INTO EMPLOYEE VALUES(101,'Manu',1000);

 

INSERT INTO EMPLOYEE VALUES(102,'Advith',2000);

COMMIT;

Syntax to insert values to selected columns

In this syntax we can selectively insert values to desired columns. NULL value will be inserted to columns excluded from insert statement. If default constraint defined for a column then default value is inserted for that excluded column. You cannot skip the column if NOT NULL constraint is defined for the columns. All columns having NOT NULL constraint must be included in insert statement.

INSERT INTO <table-name> (<column1>, <column2>, <column3>) VALUES  (<value1>, <value2>, <value3>);

COMMIT;

Example to insert values to selected columns

In the below example we are inserting values to EMPLOYEE_ID and NAME columns. SALARY column is excluded in insert statement hence NULL value is added to this column.

CREATE TABLE EMPLOYEE(

        EMPLOYEE_ID NUMBER(5),

        NAME VARCHAR2(30),

        SALARY NUMBER(6)

);

 

INSERT INTO EMPLOYEE (EMPLOYEE_ID,NAME) VALUES (103,'GANESH');

 

INSERT INTO EMPLOYEE (EMPLOYEE_ID,NAME) VALUES(104,'REVATHI');

 

COMMIT;

Syntax to insert values from another table

We can insert values reading it from another table. Select query (select query is studied in later chapter) is used to read values from source table. Select query can define the desired source columns and same order of columns to be used in insert statement.  

INSERT INTO <destination-table-name> (<column1>, <column2>, <column3>)

SELECT  [* | (<column1>, <column2>, <column3>)]

FROM <source-table-name>

[WHERE condition(s)];

Anything specified in [ ] is optional. If WHERE condition is not specified then all the records will be read from select query.

Example to insert values from another table

INSERT INTO EMPLOYEE (EMPLOYEE_ID, NAME)

SELECT CUSTOMER_ID, CUSTOMER_NAME  

FROM CUSTOMERS;

All Chapters
Author