INSERT statement in Oracle Database

Read this Oracle Tutorial Point article to know how to populate data into the SQL table in Oracle Database, we should know how to use the INSERT statement in SQL. To know more about the INSERT statement in Oracle Database, continue reading this article.

INSERT statement:
The INSERT statement is used to add rows into a

• Table
• View's Base Tables
• A partition of a partition table
• A subpartition of Composite_Partitioned Table
• An object table
• An object view’s base table

Insertion of data into a table can be executed in 2 different ways.

• Conventional INSERT
• Direct-path INSERT

In Conventional Insert statement, ORACLE reuses free space within the table into that the data in being inserted and maintain referential integrity constraints.
In Direct-path insert in ORACLE appends the inserted data after executing data in the table. The free space not reused.

Syntax:

INSERT INTO <TABLE_NAME>(col1,col2, - - ,col-n)VALUES(value1,value2, - - ,value-n);

Examples:
INSERTING DATA INTO ALL COLUMNS OF A TABLE:

SQL> INSERT INTO STUDENTS VALUES (111,’Subba’,’Reddy’,’1-Oct-90’,’M’);

In this case, the values should provide all the columns that exist inside the table. The order in which the values are declared in the values clause should follow the same order as that of the initial table order. The char, varchar and Date type data should be declared in single quotes.

INSERTING DATA INTO THE SPECIFIED COLUMNS:

SQL> INSERT INTO STUDENTS (SID, FNAME, LNAME, DOJ, GENDER) 

     VALUES (123,’Nalla’,’Machu’,’19-APR-89’,’M’);

In this case, the order of the columns declared in the SELECT list need not be the same order as that of the initial table. The data values in the VALUES clause ought to match with that SELECT list. The columns for which the data is not provided are filled with NULL values. The INSERT statement will get rejected, if the columns that weren't provided with data are defined with NOT NULL constraints.

INSERTING NULL VALUES:
NULL values can be inserted into two ways.

1. IMPLICIT – Omit the column from the list.
2. EXPLICIT – Specify the NULL keyword. Specify the Empty String (‘ ‘) only for character Strings and Dates.

The targeting column should not be set as NOT NULL constraint.

SQL> INSERT INTO STUDENTS (SID, FNAME, LNAME, DOB, DOJ, FEES, GENDER) 

     VALUES (359,’Subba’, NULL,’01-OCT-90’, NULL,’M’);

INSERTING SPECIAL VALUES:
SYSDATE FUNCTION:
It's a pseudo column provided by the ORACLE. The function returns the current date and time.

SQL> INSERT INTO STUDENTS (SID, FNAME, DOJ, FEES, GENDER) 

     VALUES (359,’Subba’, SYSDATE, 25000,’M’);

USER FUNCTION:
It's a special function which records the present USER name.

SQL> INSERT INTO STUDENTS (SID, FNAME, LNAME, DOJ, DOB, FEES) 

     VALUES (359,’Subba’, User, SYSDATE,’01-OCT-90’, 30000);

SUBSTITUTION VARIABLES:
These variables are used to store values temporarily. The values can store temporarily through,

• Single Ampersand (&)
• Double Ampersand (&&)
• DEFINE and ACCEPT Commands

The single ampersand substitution variable apples for each instance when the SQL statement is create or executed. The double ampersand variables applied for all instances until that SQL statement exists.

USING SINGLE AMPERSAND SUBSTITUTION VARIABLE:

SQL> INSERT INTO STUDENT (SID, FNAME, LNAME, DOB, FEES) 

     VALUES (&SID, ‘&FNAME’,’&LNAME’, ’&DOB’, 25000);

USING DOUBLE AMPERSAND SUBSTITUTION:

SQL> INSERT INTO STUDENT (SID, FNAME, LNAME, DOB, DOJ, FEES, GENDER) 

     VALUES (&SID, ‘&FNAME’,’&LNAME’, ’&DOB’, SYSDATE, &&FEES, ‘&GENDER’);

DEFINING CUSTOMIZED PROMPTS:
The prompt can be customized by using the ACCEPT command of SQL * Plus. ACCEPT Command stores the values in a variable. PROMPT command displays the custom text. When creating a script using the substitutions variable to a file it can called with ACCEPT and PROMPT with greater meaning.

Example:

• ACCEPT SID PROMPT ‘Please Enter the Student Number’.
• ACCEPT FNAME PROMPT ‘Please Enter the First Name’.
• ACCEPT FEES PROMPT ‘Please Enter the Course Fees’.

SQL> INSERT INTO STUDENTS (SID, FNAME, FEES) VALUES (&SID, ‘&FNAME’, &FEES);