Learn how to insert records into a table in SQL with the INSERT INTO statement. This article includes examples and code snippets to help you get started.
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.
INSERT
statementYou 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, ...);
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:
INSERT
StatementWe 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:
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:
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:
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:
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:
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:
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:
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.
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.
Source: https://www.freecodecamp.org
#sql #database