SQL Insert

 

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 one or more records into a table. There are 2 syntaxs 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!

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:

cat_id	cat_name
25	Beverages
50	Baby
75	Bakery
100	Cookies
125	Meat and Sea Food

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

INSERT INTO categories
(cat_id, cat_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:

cat_id	cat_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 cat_id of 150 and a cat_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:

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 emp with the following data:

empno	ename	job	sal	deptno
7369	Smith	CLERK	800	20
7499	ALLEN	SALESMAN	1600	30
7521	WARD	SALESMAN	1250	30
7566	JOHNES	MANAGER	2975	20

And a table called customers with the following data:

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	google.com
5000	Smith	Jane	stackoverflow.com
6000	Ferguson	Samantha	bing.com
7000	Reynolds	Allen	google.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	microsoft.com

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

INSERT INTO customers
(cust_id, last_name)
SELECT empno AS cust_id, ename
FROM emp
WHERE empno > 7876;

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

There will be 3 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	microsoft.com
5000	Smith	Jane	google.com
6000	Ferguson	Samantha	bing.com
7000	Reynolds	Allen	stackoverflow.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	microsoft.com
1001	Smith	John	NULL
1002	Anderson	Jane	NULL

In this example, the last 3 records in the customers table have been inserted using data from the emp 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

SELECT COUNT(*)
FROM emp
WHERE empno > 7876;

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

COUNT(*)
3