tech on the net
Home About Us Feedback Site Map

Microsoft

Access Excel Word

Database

SQL Oracle / PLSQL SQL Server MySQL MariaDB PostgreSQL

Web Development

HTML CSS Color Picker

Language

C Language

More

ASCII Table Linux UNIX Java Clipart Joke of the Moment
Share this page:

SQL: INSERT Statement

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.

Description

The SQL INSERT statement is used to insert a one or more records into a table.

Syntax

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;

Parameters or Arguments

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.

Note

  • When inserting records into a table using the SQL INSERT statement, you must provide a value for every NOT NULL column.
  • You can omit a column from the SQL INSERT statement if the column allows NULL values.

Example - Using VALUES keyword

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.

For example:

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.

Example - Using SELECT statement

You can also create more complicated SQL INSERT statements using SELECT statement.

For example:

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

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.

Practice Exercise #1:

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

Solution for Practice Exercise #1:

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

Practice Exercise #2:

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

Solution for Practice Exercise #2:

The following SQL INSERT statement would insert this record into the suppliers table:

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

Practice Exercise #3:

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

Solution for Practice Exercise #3:

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