Using INSERT Statement in Oracle

This article is written about how to use the Oracle INSERT statement with syntax and examples. We’ve additionally added some exercise exercises that you can attempt for yourself.

Description

The Oracle INSERT statement is used to insert a single record or a couple of records into a table in Oracle.

Syntax

The syntax for the Oracle INSERT statement when inserting a single document the use of the VALUES keyword is:

INSERT INTO table
(column1, column2, ... column_n )
VALUES
(expression1, expression2, ... expression_n );

Or the syntax for the Oracle INSERT statement when inserting a couple of information using a SELECT statement is:

INSERT INTO table
(column1, column2, ... column_n )
SELECT expression1, expression2, ... expression_n
FROM source_table
[WHERE conditions];

Parameters or Arguments

table The desk to insert the data into. column1, column2, … column_n The columns in the desk to insert values. expression1, expression2, … expression_n The values to assign to the columns in the table. So column1 would be assigned the price of expression1, column2 would be assigned the value of expression2, and so on. source_table The source desk when inserting records from every other table. WHERE conditions Optional. The stipulations that must be met for the information to be inserted.

Note

When inserting archives into a table the use of the Oracle INSERT statement, you need to provide a fee for every NOT NULL column. You can miss a column from the Oracle INSERT assertion if the column permits NULL values.

Example – Using VALUES keyword

The simplest way to create an Oracle INSERT query to listing the values using the VALUES keyword.

For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(5000, 'Apple');

This Oracle INSERT statement would result in one report being inserted into the suppliers table. This new file would have a supplier_id of 5000 and a supplier_name of ‘Apple’.

Example – Using SELECT statement

You can also create greater complex Oracle INSERT statements the usage of SELECT statements.

For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE customer_id > 5000;

By placing a SELECT declaration within the INSERT statement, you can operate multiples inserts quickly.

With this kind of insert, you may also desire to check for the range of rows being inserted. You can determine the range of rows that will be inserted with the aid of running the following Oracle SELECT declaration earlier than performing the insert.

SELECT count(*)
FROM customers
WHERE customer_id > 5000;

Frequently Asked Questions

Question: I am setting up a database with clients. I be aware of that you use the Oracle INSERT statement to insert information in the database, but how do I make positive that I do now not enter the identical consumer information again?

Answer: You can make positive that you do now not insert duplicate facts via using the EXISTS condition.

For example, if you had a table named purchasers with a predominant key of client_id, you could use the following Oracle INSERT statement:

INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers
WHERE NOT EXISTS (SELECT *
                  FROM clients
                  WHERE clients.client_id = suppliers.supplier_id);

This Oracle INSERT announcement inserts a couple of information with a subselect.

If you desired to insert a single record, you could use the following Oracle INSERT statement:

INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE NOT EXISTS (SELECT *
                  FROM clients
                  WHERE clients.client_id = 10345);

The use of the dual table lets in you to enter your values in a choose statement, even even though the values are now not presently saved in a table.

Question: How can I insert a couple of rows of explicit statistics in one INSERT command in Oracle?

Answer: The following is an instance of how you might insert 3 rows into the suppliers desk in Oracle, the use of an Oracle INSERT statement:

INSERT ALL
  INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
  INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
  INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google')
SELECT * FROM dual;

Practice Exercise #1:

Based on the contacts table, insert a contact document whose contact_id is 1000, last_name is Smith, first_name is Jane, and tackle is 10 Somewhere St.:

CREATE TABLE contacts
( contact_id number(10) not null,
  last_name varchar2(50) not null,
  first_name varchar2(50) not null,
  address varchar2(50),
  city varchar2(50),
  state varchar2(20),
  zip_code varchar2(10),
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);

Solution for Practice Exercise #1:

The following Oracle INSERT statement would insert this file into the personnel table:

INSERT INTO contacts
(contact_id, last_name, first_name, address)
VALUES
(1000, 'Smith', 'Jane', '10 Somewhere St.');

Practice Exercise #2:

Based on the contacts and customers table, insert into the contacts table all customers who live in the country of ‘Florida’.

CREATE TABLE contacts
( contact_id number(10) not null,
  last_name varchar2(50) not null,
  first_name varchar2(50) not null,
  address varchar2(50),
  city varchar2(50),
  state varchar2(20),
  zip_code varchar2(10),
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);

CREATE TABLE customers
( customer_id number(10) not null,
  last_name varchar2(50) not null,
  first_name varchar2(50) not null,
  address varchar2(50),
  city varchar2(50),
  state varchar2(20),
  zip_code varchar2(10),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

Solution for Practice Exercise #2:

The following Oracle INSERT declaration would insert this record into the suppliers table:

INSERT INTO contacts
(contact_id, last_name, first_name, address, city, state, zip_code)
SELECT customer_id, last_name, first_name, address, city, state, zip_code
FROM customers
WHERE state = 'Florida';

Since the variety of fields in the contacts and customers table are the same and the fields are listed in the equal order, you may want to write the solution as follows (though it is commonly better practice to list the column names in case the desk definitions change):

INSERT INTO contacts
SELECT *
FROM customers
WHERE state = 'Florida';