What Is the INSERT Statement in SQL?

The INSERT statement in SQL is used to add new data to your database. This article will explain how to use it and show you practical examples along the way.

When you learn SQL, you focus on getting data out of a database, working mostly with SELECT. In SQL, there are also statements that let you modify data in a database.

The first one is the INSERT statement: it adds data to a table. The data is added as a new row in the table.

The SQL INSERT statement inserts one or more rows of data into a table. You can also see INSERT written as INSERT INTO, but both behave the same. The inclusion of INTO is optional in most variants of SQL.

The INSERT statement is considered a SQL data manipulation command. The other commands are UPDATE and DELETE, which you can learn about in this accessible How to INSERT, UPDATE, or DELETE Data in SQL course from LearnSQL.com.

SQL INSERT Syntax

The syntax of SQL INSERT has two forms:

1. Using INSERT with column names.
2. Using INSERT without column names.

Let’s look at the subtle differences between the two and the rules that apply to each syntax.

SQL INSERT Syntax With Column Names

The syntax for using INSERT with column names specified is as follows:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

When specifying columns like this, the columns and the values must correspond in order, data type, and number. If you are inserting a string value, it must be enclosed in quotes. Numeric values do not need to be in quotes.

For example, imagine you had a table with the following columns:

CREATE TABLE person (name varchar(255), age int, date_of_birth date);

We must write our SQL query to take the data type of each column into account:

INSERT INTO person (name, age, date_of_birth)
VALUES ('John Doe', 25, '1996-02-18');

When including column names like this, you may be able to omit certain values depending on the table definition. For example, if the table columns allow NULL values, or have been set up with default values, you can exclude these columns from your INSERT statement entirely.

Imagine the following table was created, with a DEFAULT value provided for the date_of_birth column:

CREATE TABLE person (name varchar(255), age int, date_of_birth date DEFAULT ('1900-01-01'));

Now, if you were to write the following query, excluding the date_of_birth column entirely from our INSERT INTO statement, it will be populated with the DEFAULT value instead of with NULL.

INSERT INTO person (name, age)
VALUES ('John Doe', 25);

View the result of the INSERT by selecting from your table:

SELECT * from person

Time for us to see how the syntax changes when you use INSERT without specifying the column names. There are also ways to insert different types of data like XML into SQL Tables which might be a useful tool for you.

SQL INSERT Syntax Without Column Names

The syntax for using INSERT without column names is as follows:

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

If you choose not to list the column names, you must insert values into each column of your table, making sure to maintain the correct order as you do so. You can find the correct order of the columns by looking at the information schema for your table.

Let’s apply this syntax to a practical example. Let’s create a simple table as we did before:

CREATE TABLE person (name varchar(255), age int, date_of_birth date);

This creates our table, but how do we add multiple rows of data to it without specifying the column names? You can simply write this query:

INSERT INTO person
VALUES ('John Doe', 25, '1996-02-18'),
VALUES ('Ben Watson', 31, '1990-04-16'),
VALUES ('Lucy Smith', 27, '1993-06-29');

Note how the values are listed in the order of the table definition: first, the name of the person, then the age, and finally the date of birth. You can’t omit any columns with this syntax; you must provide a value for each column. Note that commas are used to separate values within each row as well as to separate the values of one row from those of another, with a semicolon to end the statement. That’s how we can add multiple rows without needing to use the column names themselves.

How to INSERT INTO an Existing Table

For these examples, imagine we have an existing table called people. It contains the first name, the last name, and the age of the person.

people

So, how do we add a new person to this table? Look at this SQL query below:

INSERT INTO people (first_name, last_name, age)
VALUES ('Sandra', 'Cruz', 28);

Note our adherence to the rules we mentioned earlier. The order, data type, and the number of columns and values match. The string values are also enclosed in quotes for first_name and last_name. Not including these quotes would result in an error.

We’ve successfully added one person to our table. But imagine we had to add multiple people. Writing out multiple INSERT statements would be very time consuming. Thankfully, there is a better way.

Let’s look at an example of adding multiple rows of data to our table, where we do not specify the column names.

INSERT INTO people
VALUES
	('Jacob', 'Ward', 40),
	('Stacey', 'Tuite', 65),
('Thomas', 'Rice', 43);

You can see how much time this syntax can save if you plan on adding a lot of rows to your table at once.

For more examples and an in-depth explanation, you may consider taking a course that focuses on the topic. Check out this blog post which explains the benefits of enrolling in a course that specializes in the topic of INSERT, UPDATE, and DELETE commands. When replicating these examples, it is important to understand how permissions may affect the use of the INSERT statement.

SQL INSERT Permissions

You need an INSERT permission to insert data into a database. It’s possible that you only have permission to select the data, especially if you’re working with a database for which you are not the admin. If this is the case, you will not be allowed to insert data into a table and may need to ask your database administrator for these privileges.

Add new Data to Your Tables With SQL INSERT

That concludes our article on SQL INSERT. We’ve discussed the subtle differences between adding single or multiple rows to your table, allowing you to add data in the most effective manner. We also touched on the permissions you may need if you are not the administrator of your database. Feel free to bookmark this page so you can easily refer back to it. We’ve mentioned how INSERT is one of the ways to alter the data inside your tables. The other commands that change data are UPDATE and DELETE. Learn more about them in this article about INSERT, UPDATE, and DELETE.