SQL INSERT Statement – How to Insert Data into a Table in SQL

By Karlgusta Annoh

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:


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.


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');


Here is the table with the data 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:


Here is the table with the data 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)
);


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

INSERT INTO PersonsBackup
SELECT * FROM Persons;


Here is the table with the data inserted:


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;


Here is the table with the data inserted:


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;


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:


Here is the table with the data 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:


The table now has 14 records:


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 data 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;


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:


The records have been inserted into the PersonsBackup 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;


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;


Here is the Persons table before the query is run:


Here is 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:


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:

Here is 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, 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.


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


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.