How to Use the SQL INSERT Statement

Have you ever wondered how data is added to a database? Behind every record in a database is an INSERT INTO statement. Without this statement, databases could not store data!

In this guide, we’re going to talk about what the INSERT INTO command is and how it works. We’ll walk through a few example queries to help you get started using INSERT INTO.

Let’s begin!

What is SQL INSERT INTO?

The INSERT INTO statement adds a record into a database.

The statement accepts a list of the column names into which you want to insert values in a record and a list of the corresponding values that you want to set:

INSERT INTO table (column1, column2…)
VALUES (value1, value2…);

The items in the first set of parentheses are the table column names. The items in the second set of parentheses are the values you want to insert into your record in those columns.

It’s no surprise this statement is called INSERT INTO: it allows you to insert a record into a database.

Let’s start by creating a sample table called employees:

CREATE TABLE employees (
	id INTEGER AUTO INCREMENT,
	name VARCHAR(75),
	title VARCHAR(75),
	hired_date DATE,
	salary INTEGER
);

This command creates a table with four columns: name, title, hired_date and salary. Let’s insert a record into this database!

INSERT INTO in Action

We’re starting to populate the employees database with a few records. Our database currently contains the following records:

We want to add in a new record to this database with the following values:

To add in this record, we can use an INSERT INTO statement. Open up an SQL shell and paste in the following statement:

INSERT INTO employees (name, title, hired_date, salary)
VALUES ('Victoria Carlisle', 'Sales Director', '05-02-2017', 36000);

The values you specify must correspond to the data types of the columns in a table. The salary column is structured as an integer. We have specified an integer value for salaries. The hired_date column is formatted as a date. We have specified a date value.

This command adds in our record to the database. Let’s select all the records in our employees database:

Victoria Carlisle has been added to the database. Notice how we didn’t specify an employee ID for Victoria. That is because we used an AUTO INCREMENT statement when we created our table. This statement automatically increments the value of ID by one for each new record created in the table.

If you are inserting values into every column in a database, you do not need to specify what columns you are adding data into. This is because SQL can assume based on the number of values specified that they will fill the database.

Our above command can be shortened to:

INSERT INTO employees
VALUES ('Victoria Carlisle', 'Sales Director', '05-02-2017', 36000);

INSERT INTO Using Specific Columns

You can insert data into a database without specifying a value for all columns. Let’s create a record for a new employee whose title is being changed:

INSERT INTO employees VALUES (name, hired_date, salary)
VALUES ('Justin Peters', '04-11-2018', 33000);

Our database now contains the following values:

We did not specify a title for Justin Peters so the value was set to null by default. This only works if a table supports null values. Otherwise, an error will be returned by the SQL shell.

INSERT INTO SELECT Statement

You can insert records into a table that are stored elsewhere in a database. This is useful if you need to create a copy of particular records from one table, or if you need to move the contents of one table into another table.

The INSERT INTO SELECT statement is a hybrid between the INSERT INTO and SELECT statements:

INSERT INTO table2 (column1, column2…)
SELECT column1, column2…
FROM table1
WHERE condition;

Let’s say that we want to copy all sales associates into a new table. We could do this using the following command:

INSERT INTO sales_associates (name, hired_date, salary)
SELECT name, hired_date, salary
FROM employees
WHERE title = 'Sales Associate';

This will insert a list of all sales associates’ names, hired dates, and salaries into the sales_associate table:

Conclusion

The INSERT INTO statement adds a record into an SQL database. You do not need to specify all the values that a record can hold when you use INSERT INTO. You can leave some values blank and come back to them later, as long as null values are allowed.

As a challenge, write queries for the following:

• Add an employee called Sally Pringle with the title Sales Associate to the database. She does not have a hired date or salary because she has not yet started.
• Add an employee called Lucas Patterson with the title Marketing Manager to the database. He was hired on 22-04-2019 and has a salary of 36000.

Now you’re ready to start using the SQL INSERT INTO statement like an SQL server pro! 

"Career Karma entered my life when I needed it most and quickly helped me match with a bootcamp. Two months after graduating, I found my dream job that aligned with my values and goals in life!"

Venus, Software Engineer at Rockbot

Find Your Bootcamp Match