totn SQL

SQL: INSERT Statement

This SQL tutorial explains how to use the SQL INSERT statement with syntax, examples, and practice exercises.

Description

The SQL INSERT statement is used to insert a one or more records into a table. There are 2 syntaxes for the INSERT statement depending on whether you are inserting one record or multiple records.

subscribe button Subscribe

Syntax

The syntax for the INSERT statement when inserting a single record in SQL is:

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

Or the syntax for the INSERT statement when inserting multiple records in SQL is:

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

Parameters or Arguments

table
The table in which to insert the records.
column1, column2
These are the columns in the table to insert values.
expression1, expression2
These 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
Used when inserting records from another table. This is the source table when performing the insert.
WHERE conditions
Optional. Used when inserting records from another table. These are the 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 only omit a column from the INSERT statement if the column allows NULL values.

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Get DDL/DML

Example - Using INSERT Statement to Insert One Record

The simplest way use the INSERT statement is to insert one record into a table using the VALUES keyword. Let's look at an example of how to do this in SQL.

In this example, we have a table called categories with the following data:

category_id category_name
25 Deli
50 Produce
75 Bakery
100 General Merchandise
125 Technology

Let's insert a new category record. Enter the following SQL statement:

Try It
INSERT INTO categories
(category_id, category_name)
VALUES
(150, 'Miscellaneous');

There will be 1 record inserted. Select the data from the categories table again:

SELECT * FROM categories;

These are the results that you should see:

category_id category_name
25 Deli
50 Produce
75 Bakery
100 General Merchandise
125 Technology
150 Miscellaneous

This example would insert one record into the categories table. This new record would have a category_id of 150 and a category_name of 'Miscellaneous'.

In this example, because you are providing values for all of the columns in the categories table, you could omit the column names and instead write the INSERT statement like this:

Try It
INSERT INTO categories
VALUES
(150, 'Miscellaneous');

However, this is dangerous to do for 2 reasons. First, the INSERT statement will error if additional columns are added to the categories table. Second, the data will be inserted into the wrong columns if the order of the columns change in the table. So as a general rule, it is better to list the column names in the INSERT statement.

Example - Using INSERT Statement to Insert Multiple Records

By placing a SELECT statement within the INSERT statement, you can perform multiples inserts quickly. Let's look at an example of how to do this.

In this example, we have a table called employees with the following data:

employee_number last_name first_name salary dept_id
1001 Smith John 62000 500
1002 Anderson Jane 57500 500
1003 Everest Brad 71000 501
1004 Horvath Jack 42000 501

And a table called customers with the following data:

customer_id last_name first_name favorite_website
4000 Jackson Joe techonthenet.com
5000 Smith Jane digminecraft.com
6000 Ferguson Samantha bigactivities.com
7000 Reynolds Allen checkyourmath.com
8000 Anderson Paige NULL
9000 Johnson Derek techonthenet.com

Now, let's insert some of the employee information into the customers table:

Try It
INSERT INTO customers
(customer_id, last_name, first_name)
SELECT employee_number AS customer_id, last_name, first_name
FROM employees
WHERE employee_number < 1003;
TIP: With this type of INSERT, some databases require you to alias the column names in the SELECT to match the column names of the table you are inserting into. As you can see in the example above, we have aliased the first column in the SELECT statement to customer_id.

There will be 2 records inserted. Select the data from the customers table again:

SELECT * FROM customers;

These are the results that you should see:

customer_id last_name first_name favorite_website
4000 Jackson Joe techonthenet.com
5000 Smith Jane digminecraft.com
6000 Ferguson Samantha bigactivities.com
7000 Reynolds Allen checkyourmath.com
8000 Anderson Paige NULL
9000 Johnson Derek techonthenet.com
1001 Smith John NULL
1002 Anderson Jane NULL

In this example, the last 2 records in the customers table have been inserted using data from the employees table.

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 a COUNT(*) on the SELECT statement before performing the insert. For example:

Try It
SELECT COUNT(*)
FROM employees
WHERE employee_number < 1003;

This will return number of records that will be inserted when you execute the INSERT statement.

COUNT(*)
2

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 AS client_id, supplier_name AS client_name, 'advertising' AS client_type
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 Exercises

If you want to test your skills using the SQL INSERT statement, try some of our practice exercises.

These exercises allow you to try out your skills with the INSERT statement. You will be given questions that you need to solve. After each exercise, we provide the solution so you can check your answer. Give it a try!

Go to Practice Exercises