Oracle/PLSQL: INSERT Statement

SQL: INSERT Statement

The SQL INSERT statement allows you to insert a single record or multiple records into a table.

The syntax for the SQL INSERT statement is:

INSERT INTO table

(column-1, column-2, ... column-n)

VALUES

(value-1, value-2, ... value-n);

SQL INSERT Statement - Using VALUES keyword example

The simplest way to create an SQL INSERT statement to list the values using the VALUES keyword.

For example:

INSERT INTO suppliers

(supplier_id, supplier_name)

VALUES

(24553, 'IBM');

This SQL INSERT statement would result in one record being inserted into the suppliers table. This new record would have a supplier_id of 24553 and a supplier_name of IBM.

SQL INSERT Statement - Using sub-selects example

You can also create more complicated SQL INSERT statements using sub-selects.

For example:

INSERT INTO suppliers

(supplier_id, supplier_name)

SELECT account_no, name

FROM customers

WHERE city = 'Newark';

By placing a "select" in the SQL INSERT statement, you can perform multiples inserts quickly.

With this type of insert, you may wish to check for the number of rows being inserted. You can determine the number of rows that will be inserted by running the following SQL SELECT statement before performing the insert.

SELECT count(*)

FROM customers

WHERE city = 'Newark';

Frequently Asked Questions

---

Question: I am setting up a database with clients. I know that you use the SQL INSERT statement to insert information in the database, but how do I make sure that I do not enter the same client information again?

Answer: You can make sure that you do not insert duplicate information by using the SQL EXISTS condition.

For example, if you had a table named clients with a primary key of client_id, you could use the following SQL 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 SQL INSERT statement inserts multiple records with a subselect.

If you wanted to insert a single record, you could use the following SQL 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 allows you to enter your values in a select statement, even though the values are not currently stored in a table.

---

Question: How can I insert multiple rows of explicit data in one SQL command in Oracle?

Answer: The following is an example of how you might insert 3 rows into the suppliers table in Oracle, using an SQL 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;