Learn how to use the SQL INSERT statement with syntax, examples, and practice exercises. There are 2 syntaxes for the INSERT statement depending on whether you are inserting one record or multiple records.
The SQL INSERT statement is used to insert a one or more records into a table.
The syntax for the SQL INSERT statement when inserting a single record using the VALUES keyword is:
INSERT INTO table (column1, column2, ... ) VALUES (expression1, expression2, ... );
Or the syntax for the SQL INSERT statement when inserting multiple records using a SELECT statement is:
INSERT INTO table (column1, column2, ... ) SELECT expression1, expression2, ... FROM source_tables WHERE conditions;
table is the table to insert the records into.
column1, column2 are the columns in the table to insert values.
expression1, expression2 are the values to assign to the columns in the table. So column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and so on.
source_tables is the source table when inserting data from another table.
conditions are conditions that must be met for the records to be inserted.
Let's look at an example showing how to use the SQL INSERT statement. The simplest way use the INSERT statement is to insert one record into a table using the VALUES keyword.
INSERT INTO suppliers (supplier_id, supplier_name) VALUES (24553, 'IBM');
This INSERT statement example would insert one record into the suppliers table. This new record would have a supplier_id of 24553 and a supplier_name of IBM.
You can also create more complicated SQL INSERT statements using SELECT statement.
INSERT INTO suppliers (supplier_id, supplier_name) SELECT account_no, name FROM customers WHERE city = 'Newark';
By placing a SELECT statement within the 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';
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.
Based on the employees table, insert an employee record whose employee_number is 1001, employee_name is Sally Johnson and salary is $32,000:
CREATE TABLE employees ( employee_number number(10) not null, employee_name varchar2(50) not null, salary number(6), CONSTRAINT employees_pk PRIMARY KEY (employee_number) );
The following SQL INSERT statement would insert this record into the employees table:
INSERT INTO employees (employee_number, employee_name, salary) VALUES (1001, 'Sally Johnson', 32000);
Based on the suppliers table, insert a supplier record whose supplier_id is 5001 and supplier_name is Apple:
CREATE TABLE suppliers ( supplier_id number(10) not null, supplier_name varchar2(50) not null, city varchar2(50), CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id) );
The following SQL INSERT statement would insert this record into the suppliers table:
INSERT INTO suppliers (supplier_id, supplier_name) VALUES (5001, 'Apple');
Based on the customers and old_customers table, insert into the customers table all records from the old_customers table whose status is DELETED.
CREATE TABLE customers ( customer_id number(10) not null, customer_name varchar2(50) not null, city varchar2(50), CONSTRAINT customers_pk PRIMARY KEY (customer_id) ); CREATE TABLE old_customers ( old_customer_id number(10) not null, old_customer_name varchar2(50) not null, old_city varchar2(50), status varchar2(20), CONSTRAINT old_customers_pk PRIMARY KEY (old_customer_id) );
The following SQL INSERT statement would be the solution to insert into the customers table using a sub-select:
INSERT INTO customers (customer_id, customer_name, city) SELECT old_customer_id, old_customer_name, old_city FROM old_customers WHERE status = 'DELETED';