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.
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!
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 ItINSERT 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 ItINSERT 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 ItINSERT INTO customers (customer_id, last_name, first_name) SELECT employee_number AS customer_id, last_name, first_name FROM employees WHERE employee_number < 1003;
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 ItSELECT 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!
Advertisements