In this tutorial, you'll learn how to use the SQL INSERT statement.

We'll discuss the syntax of INSERT, and then we'll use an example to show all the different ways you can use INSERT. We'll also combine it with other helpful clauses to perform more complex operations.

Prerequisites

  • Basic understanding of SQL

Syntax of SQL INSERT statement

You use the SQL INSERT INTO statement to insert new records in a table. The syntax of the SQL INSERT INTO statement is:

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

Example of SQL INSERT

Let's say we have a table called Persons with the following columns:

  • PersonID
  • LastName
  • FirstName
  • Address
  • City

Let's first create the table:

Create a table in SQL

I am using this query to create the table:

CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

On running the query will create the table.

Empty table we created

We can insert a new record into the Persons table by using the following SQL statement:

INSERT INTO Persons (PersonID, LastName, FirstName, Address, City) 
VALUES (1, 'Wilson', 'John', '123 Main St.', 'Anytown');

Inserting data into persons table

Here is the table with the data inserted:

Table after data has been inserted

How to Insert Multiple Records with the INSERT Statement

We can insert multiple records into a table by using a single SQL statement. The following SQL statement inserts three new records into the Persons table:

INSERT INTO Persons (PersonID, LastName, FirstName, Address, City)
VALUES (1, 'Wilson', 'John', '123 Main St.', 'Anytown'),
       (2, 'Smith', 'Mary', '456 Maple St.', 'Anytown'),
       (3, 'Jones', 'David', '789 Elm St.', 'Anytown'),
       (4, 'John', 'David', '789 Elm St.', 'Meru');

When running the query on TablePlus, it will look like this:

Insert multiple records

Here is the table with the data inserted:

The table with the records inserted

How to Insert Records from Another Table

We can insert records into a table from another table using the SQL INSERT INTO SELECT statement.

The following SQL statement inserts all records from the Persons table into the PersonsBackup table:

INSERT INTO PersonsBackup
SELECT * FROM Persons;

In order to run this query, we need to create a new table called PersonsBackup:

CREATE TABLE PersonsBackup (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
    PRIMARY KEY (PersonID)
);

Create Table PersonsBackup

Now we can run the query to insert the records from the Persons table into PersonsBackup table:

INSERT INTO PersonsBackup
SELECT * FROM Persons;

Inserting from PersonsBackup from Persons table

Here is the table with the data inserted:

Records inserted from PersonsBackup from Persons table

How to Insert Records from a SELECT Statement

We can insert records from a SELECT statement into a table by using the SQL INSERT INTO SELECT statement. The following SQL statement inserts all records from the Persons table into the PersonsBackup table:

INSERT INTO PersonsBackup
SELECT * FROM Persons;

Inserting from PersonsBackup from Persons table

Here is the table with the data inserted:

Records inserted from PersonsBackup from Persons table

How to Insert Records from a SELECT Statement with a WHERE Clause

We can insert records into a table from a SELECT statement with a WHERE clause by using the SQL INSERT INTO SELECT statement.

The following SQL statement inserts all records from the Persons table into the PersonsBackup table where the City is Anytown:

Let's first delete the records from the PersonsBackup table:

DELETE FROM PersonsBackup;

Deleting records from PersonsBackup

Now that the records have been deleted, we can insert the records from the Persons table into the PersonsBackup table where the City is Anytown:

INSERT INTO PersonsBackup
SELECT * FROM Persons WHERE City = 'Anytown';

Inserting records from the Persons table into the PersonsBackup table where the City is Anytown:

Record inserted from Persons table into PersonsBackup table where the City id Anytown

Here is the table with the data inserted:

Table where the data has been inserted

How to Insert Records from a SELECT Statement with a WHERE Clause and a LIMIT Clause

We can insert records into a table from a SELECT statement with a WHERE clause and a LIMIT clause by using the SQL INSERT INTO SELECT statement.

The following SQL statement inserts the first 10 records from the Persons table into the PersonsBackup table where the City is Anytown:

Let's first create at least 10 records in the Persons table where the City is Anytown:

INSERT INTO Persons(PersonID, LastName, FirstName, Address, City) 
VALUES (5, 'Wilson', 'John', '123 Main St.', 'Anytown'),
       (6, 'Smith', 'Mary', '456 Maple St.', 'Anytown'),
       (7, 'Jones', 'David', '789 Elm St.', 'Anytown'),
       (8, 'John', 'David', '789 Elm St.', 'Anytown'),
       (9, 'Wilson', 'John', '123 Main St.', 'Anytown'),
       (10, 'Smith', 'Mary', '456 Maple St.', 'Anytown'),
       (11, 'Jones', 'David', '789 Elm St.', 'Anytown'),
       (12, 'John', 'David', '789 Elm St.', 'Anytown'),
       (13, 'Wilson', 'John', '123 Main St.', 'Anytown'),
       (14, 'Smith', 'Mary', '456 Maple St.', 'Anytown');

The values have been inserted into the Persons table:

Insert into Persons table

The table now has 14 records:

The data inserted into Persons table

We can also add records with the city name being anything other than Anytown:

INSERT INTO Persons(PersonID, LastName, FirstName, Address, City)
VALUES (15, 'Jones', 'David', '789 Elm St.', 'New York'),
       (16, 'John', 'David', '789 Elm St.', 'New York'),
       (17, 'Wilson', 'John', '123 Main St.', 'New York'),
       (18, 'Smith', 'Mary', '456 Maple St.', 'New York');

The values have been inserted into the Persons table:

The values inserted into the Persons table

The data with different cities has been inserted into the Persons table:

The records with different cities has been inserted into the Persons table

Now that we have at least 10 records in the Persons table where the City is Anytown, we can insert the first 10 records from the Persons table into the PersonsBackup table where the City is Anytown:

We will first delete the records from the PersonsBackup table:

DELETE FROM PersonsBackup;

Deleting from PersonsBackup table

The PersonsBackup table is now empty:

The PersonsBackup table is now empty

We can now insert the first 10 records from the Persons table into the PersonsBackup table where the City is Anytown:

INSERT INTO PersonsBackup
SELECT * FROM Persons WHERE City = 'Anytown' LIMIT 10;

You use the limit clause to limit the number of records to be inserted into the PersonsBackup table. In this case, we are inserting the first 10 records from the Persons table into the PersonsBackup table where the City is Anytown.

You use the where clause to specify the condition that must be met for the records to be inserted into the PersonsBackup table. In this case, the City must be Anytown for the records to be inserted into the PersonsBackup table.

When we run the above query, the first 10 records from the ePersons table where the City is Anytown will be inserted into the PersonsBackup table:

Running the above query:

Running the above query, the first 10 records from Persons table where the City is Anytown will be inserted into PersonsBackup table

The records have been inserted into the PersonsBackup table:

The 10 records inserted from Persons table

How to Insert Records from a SELECT Statement with a WHERE Clause and an ORDER BY Clause

We can insert records from a SELECT statement with a WHERE clause and an ORDER BY clause into a table by using the SQL INSERT INTO SELECT statement.

The following SQL statement inserts all records from the Persons table into the PersonsBackup table where the City is Anytown and orders the records by LastName.

Let's first delete the records from PersonsBackup table:

DELETE FROM PersonsBackup;

Delete from PersonsBackup

The PersonsBackup table is now empty.

Now, we can insert all records from the Persons table into the PersonsBackup table where the City is Anytown and order the records by LastName:

INSERT INTO PersonsBackup
SELECT * FROM Persons WHERE City = 'Anytown' ORDER BY LastName;

Inserting into PersonsBackup table from Persons table where city is Anytown and ordering by lastname

Here is the Persons table before the query is run:

The Persons table before the query is run

Here is the PersonsBackup table after the records have been inserted:

The PersonsBackup table after the records have been inserted

How to Insert Records from a SELECT Statement with a WHERE Clause, an ORDER BY Clause, and a LIMIT Clause

We can insert records into a table from a SELECT statement with a WHERE clause and an ORDER BY clause and a LIMIT clause by using the SQL INSERT INTO SELECT statement.

The following SQL statement inserts the first 10 records from the Persons table into the PersonsBackup table where the City is Anytown and orders the records by LastName.

First, let's delete the records from PersonsBackup table:

DELETE FROM PersonsBackup;

Run the query above on your database management tool. I am using TablePlus.

After running the delete query, here is the result:

Delete from PersonsBackup table

The PersonsBackup table is now empty.

INSERT INTO PersonsBackup
SELECT * FROM Persons WHERE City = 'Anytown' ORDER BY LastName LIMIT 10;

Running the above query on TablePlus:

image

Here is the PersonsBackup table after the records have been inserted:

Inserting into PersonsBackup table after selecting from Persons table where city is Anytown, being ordered by lastname and the limit is 10

How to Insert Records from a SELECT Statement with a WHERE Clause, an ORDER BY Clause, a LIMIT Clause, and an OFFSET Clause

We can insert records from a SELECT statement with a WHERE clause, an ORDER BY clause, a LIMIT clause, and an OFFSET clause by using the SQL INSERT INTO SELECT statement.

The following SQL statement inserts the records from the Persons table into the PersonsBackup table where the City is Anytown. It orders the records by LastName, limits the records to 10, and skips the first 5 records.

We'll start with the OFFSET clause. The OFFSET clause is used to skip the first n records. In this case, we are skipping the first 5 records.

First, let's delete the records from the PersonsBackup table:

DELETE FROM PersonsBackup;

Run the query above on your database management tool.

After running the delete query, here is the result.

Delete from PersonsBackup table

INSERT INTO PersonsBackup
SELECT * FROM Persons WHERE City = 'Anytown' ORDER BY LastName LIMIT 10 OFFSET 5;

Inserting into PersonsBackup table, selecting from PersonsTable where city is Anytown and ordering by lastname, while limiting to 10 and skipping the first 5 items

Since we have skipped the first 5 records, the first 5 records in the Persons table are not inserted into the PersonsBackup table. This means only 8 records out of 13 records are inserted into the PersonsBackup table, where City is equal to Anytown.

Conclusion

In this tutorial, you have learned how to insert records into a table by using the SQL INSERT INTO statement.

You have also learned how to insert records into a table with a SELECT statement by using the SQL INSERT INTO SELECT statement.

If you want to learn more about SQL, check out this course: SQL Tutorial - Full Database Course for Beginners. It's free on freeCodeCamp's YouTube channel.