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.
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.
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;