Oracle SQL INSERT – How to Use This?

Oracle SQL INSERT SQL, Structured Query Language, is the standard language used by software companies and program developers to communicate with a relational database. It is most widely used database manipulation language in business and industry. SQL is used to express your needs to the database. If you want to request specific information from within a database, you do it in the form of a query in SQL. As a developer if you are looking for ways to insert records in Oracle PL/SQL, then this discussion on INSERT statement will be quite fruitful.

In case you are looking to get acquainted with relational databases and data management at an elementary level then Database Management System, Microsoft SQL Database Fundamentals will introduce you to the preliminary concepts. If you are new to writing SQL queries and want to learn how to build applications or generate business reports using SQL, then Introduction to SQL Training is the perfect match for you.

Oracle PL/SQL INSERT Statement

In Oracle PL/SQL, an INSERT statement adds one or more records to any single table in a relational database. The INSERT statement may also be used to add rows to the base table, view, partition, subpartition, or object table. It also supports additional extensions such as inserting records into many tables at once and conditional inserts. Courses such as, Oracle PL/SQL Tutorial – A Comprehensive Training Course and SQL Database for Beginners will help you get acquainted with the exact syntax and INSERT statements.

To start with let us look at the basic INSERT statement options in SQL.

INSERT INTO – Single Column Table Or View: INTO statement is used to add one or more records to one single table. The number of columns must be equal to the number of values.

Syntax:

INSERT INTO <table_name>

(<column_name>)

VALUES

(<value>);

Example: INSERT INTO address_book (name, house number) VALUES (‘Robin Brown’, ‘202’)

Multiple Column Table Or View – All Columns: This inserts data only into certain number of columns while data appearing after these columns are ignored.

Syntax:

INSERT INTO <table_name>

VALUES

(<comma_separated_value_list>);

Example: INSERT INTO address_book VALUES (‘Robin Brown’, ‘202’);

INSERT Multiple rows in a table: This syntax is used to insert multiple rows at a time through a single SQL statement.

Syntax:

INSERT INTO <table_name>

(<comma_separated_column_name_list>)

VALUES

(<comma_separated_value_list>);

Example: INSERT INTO address_book VALUES (‘Robin Brown’, ‘202’), (‘John Roy’, ‘104’);

SQL New Features in Oracle 9i

INSERT and SELECT Statements

There are a large number of SQL enhancements in Oracle 9i. Multitable inserts are possible here, where a single INSERT INTO and SELECT statements allow to conditionally, or unconditionally insert data into multiple tables. This statement reduces table scans and PL/SQL codes necessary for performing multiple conditional inserts which appear in previous versions.

INSERT and SELECT statements are used to add rows to a table. The SELECT statement retrieves or extracts information from tables in the database. Using the SELECT statement, you can choose the rows in a table that you want returned from a query by specifying values to match the rows that you want to display. The INSERT statement with a nested SELECT statement helps a table to be quickly populated with one or more rows from the result set of the SELECT statement.

Basic SELECT Statement

In its simplest form a SELECT statement must contain the following:

• A SELECT clause: This specifies the columns which contain the values to be matched against.

• A FROM clause which specifies the TABLE containing the columns listed in the SELECT clause.

When using INSERT…SELECT between a target and a source table, it is mandatory to ensure that the datatypes returned by the SELECT statement are compatible with the datatypes in the target table.

Insert From SELECT statement:

  1. Use the INSERT statement to specify values directly or from a subquery: Using a SELECT subquery to specify the data values for one or more rows, such as: INSERT INTO <table_name> <SELECT Statement>;
  2. Use the SELECT statement with the INTO clause: To create a new table from values in another table, you can use SELECT INTO

To insert records:

Insert Using A Record:

Syntax:

INSERT INTO <table_name>

VALUES <record_name>;

INSERT with Conditions or WHEN:

Syntax:

INSERT

WHEN (<condition>) THEN

INTO <table_name> (<column_list>)

VALUES (<values_list>)

WHEN (<condition>) THEN

INTO <table_name> (<column_list>)

VALUES (<values_list>)

ELSE

INTO <table_name> (<column_list>)

VALUES (<values_list>)

SELECT <column_list> FROM <table_name>;

INSERT ALL: Without the WHEN clause INSERT ALL performs all inserts unconditionally.

Syntax:

INSERT ALL

INTO <table_name> VALUES <column_name_list)

INTO <table_name> VALUES <column_name_list)

<SELECT Statement>;

INSERT ALL WHEN: WHEN specifies the conditions for inserting.

Syntax:

INSERT ALL

WHEN (<condition>) THEN

INTO <table_name> (<column_list>)

VALUES (<values_list>)

WHEN (<condition>) THEN

INTO <table_name> (<column_list>)

VALUES (<values_list>)

ELSE

INTO <table_name> (<column_list>)

VALUES (<values_list>)

SELECT <column_list> FROM <table_name>;

INSERT FIRST WHEN: This tells Oracle to evaluate the WHEN clauses in order. When it finds the first true expression, the database executes the corresponding INTO clause and skips all other WHEN clauses.

Syntax:

INSERT FIRST

INTO <table_name> VALUES <column_name_list)

INTO <table_name> VALUES <column_name_list)

<SELECT Statement>;

INSERT WITH CHECK OPTION: WITH CHECK OPTION  indicates that Oracle prohibits any changes to the table or view that would produce rows that are not included in the subquery.

Syntax:

INSERT INTO (

<SQL_statement> WITH CHECK OPTION)

VALUES

(value_list);

INSERT With Returning Clause: Used for inserting into a table with a return clause. The RETURNING clause is not allowed with multitable insert operations.

Syntax:

INSERT INTO <table_name>

(column_list)

VALUES

(values_list)

RETURNING <value_name>

INTO <variable_name>;

Conclusion

The Oracle SQL INSERT statement is mainly used for the ETL process in data warehouses where it can be parallelized and/or convert non-relational data into a relational format. While you keep exploring the INSERT statements, you can check out Introduction to Oracle SQL and A Beginners Guide To SQL. Master SQL Quickly to learn Oracle PL/SQL Programming and the basics of using SQL language on an Oracle database.