SQL INSERT Statement
In this tutorial, we will help you to understand and learn how to insert records to the table in SQL with the help of examples.
SQL INSERT query is used to add records to the table. We can add single or multiple data to a table using the INSERT query.
We can add data to the tables by mentioning the column name in the syntax or without mentioning the column name in the syntax.
We can add records to the new table and copy the existing table data into the newly created table by using the insert into select query.
There are two ways we can add data to the table as follows:
1. SQL INSERT INTO query.
2. SQL INSERT INTO SELECT query.
Let's understand how to use the INSERT query with the help of examples.
1. SQL INSERT INTO query
SQL INSERT INTO query is used to add data to a table.
There are two ways to add data to the table by using the SQL INSERT INTO query as follows:
1. In this type, we need not mention the column's name in the syntax. We just need to mention the data to be added to the table.
2. In this type, we need to mention the specific column name in the syntax; mentioning the columns allows us to add records to the column name with their values.
1. SQL INSERT INTO query without mentioning the columns name:
The syntax of the SQL INSERT INTO query without mentioning columns name is as follows:
INSERT INTO Table_Name VALUES (Value_1, Value_2, Value_3, Value_4, Value_5, Value_6, Value_7);
We can add multiple data simultaneously into the table in the same method.
Let's take an example of this method.
Firstly, we will create a new table to add records to the table.
CREATE TABLE Player(Player_No int not null, Player_Name Varchar(100) not null, ODI_Runs int, T20_Runs int, Test_Runs int, IPL_Runs int, Best_ODI_Ranking int, Best_T20_Ranking int, Best_Test_Ranking int, Team varchar(100));
In the above query, we created the Player name table.
We will run the DESC query followed by the table name to check whether the Player table is successfully created or not:
DESC Player;
The output of the above query is as follows:
| Field | Type | Null | Key | Default | Extra |
| Player_No | int(11) | NO | NULL | ||
| Player_Name | varchar(100) | NO | NULL | ||
| ODI_Runs | int(11) | YES | NULL | ||
| T20_Runs | int(11) | YES | NULL | ||
| Test_Runs | int(11) | YES | NULL | ||
| IPL_Runs | int(11) | YES | NULL | ||
| Best_ODI_Ranking | int(11) | YES | NULL | ||
| Best_T20_Ranking | int(11) | YES | NULL | ||
| Best_Test_Ranking | int(11) | YES | NULL | ||
| Team | varchar(100) | YES | NULL |
Now, we will add records into the table is as follows:
INSERT INTO Player VALUES (10, ‘Sachin Tendulkar’, 18426, 10, 15921, 2334, 1, 63, 1, ‘India’);
INSERT INTO Player VALUES (19, 'Rahul Dravid', 10889, 31, 13288, 2174, 5, 0, 1, 'India');
INSERT INTO Player VALUES (14, 'Ricky Ponting', 13704, 401, 13378, 91, 1, 1, 1, 'Australia');
We added three records to the Player table. We will run the select query to check whether the data added successfully or not is as follows:
SELECT * FROM Player;
The output of the above query is as follows:
| Player_No | Player_Name | ODI_Runs | T20_Runs | Test_Runs | IPL_Runs | Best_ODI_Ranking | Best_T20_Ranking | Best_Test_Ranking | Team |
| 10 | Sachin Tendulkar | 18426 | 10 | 15921 | 2334 | 1 | 63 | 1 | India |
| 19 | Rahul Dravid | 10889 | 31 | 13288 | 2174 | 5 | 0 | 1 | India |
| 14 | Ricky Ponting | 13704 | 401 | 13378 | 91 | 1 | 1 | 1 | Australia |
2. SQL INSERT INTO query with mentioning the columns name:
The syntax of the SQL INSERT INTO query with mentioning columns name is as follows:
INSERT INTO Table_Name (Column_Name1, Column_Name2, Column_Name3, Column_Name4, Column_Name5, Column_Name6, Column_Name7) VALUES (Value_1, Value_2, Value_3, Value_4, Value_5, Value_6, Value_7);
Let’s take an example on this method.
INSERT INTO Player (Player_No, Player_Name, ODI_Runs, T20_Runs, Test_Runs, IPL_Runs, Best_ODI_Ranking, Best_T20_Ranking, Best_Test_Ranking, Team) VALUES (18, ‘Virat Kohli’, 12327, 3308, 8074, 6624, 1, 1, 1, ‘India’);
INSERT INTO Player (Player_No, Player_Name, ODI_Runs, T20_Runs, Test_Runs, IPL_Runs, Best_ODI_Ranking, Best_T20_Ranking, Best_Test_Ranking, Team) VALUES (45, ‘Rohit Sharma’, 9359, 3379, 3137, 5879, 2, 7, 5, ‘India’);
INSERT INTO Player (Player_No, Player_Name, ODI_Runs, T20_Runs, Test_Runs, IPL_Runs, Best_ODI_Ranking, Best_T20_Ranking, Best_Test_Ranking, Team) VALUES (66, ‘Joe Root’, 6120, 893, 10458, 0, 2, 4, 1, ‘England’);
INSERT INTO Player (Player_No, Player_Name, ODI_Runs, T20_Runs, Test_Runs, IPL_Runs, Best_ODI_Ranking, Best_T20_Ranking, Best_Test_Ranking, Team) VALUES (22, ‘Kane Williamson’, 6174, 2021, 7368, 2101, 3, 3, 1, ‘New Zealand’);
We added Four records to the Player table. We will run the select query to check whether the data added successfully or not is as follows:
SELECT * FROM Player;
The output of the above query is as follows:
| Player_No | Player_Name | ODI_Runs | T20_Runs | Test_Runs | IPL_Runs | Best_ODI_Ranking | Best_T20_Ranking | Best_Test_Ranking | Team |
| 10 | Sachin Tendulkar | 18426 | 10 | 15921 | 2334 | 1 | 63 | 1 | India |
| 19 | Rahul Dravid | 10889 | 31 | 13288 | 2174 | 5 | 0 | 1 | India |
| 14 | Ricky Ponting | 13704 | 401 | 13378 | 91 | 1 | 1 | 1 | Australia |
| 18 | Virat Kohli | 12327 | 3308 | 8074 | 6624 | 1 | 1 | 1 | India |
| 45 | Rohit Sharma | 9359 | 3379 | 3137 | 5879 | 2 | 7 | 5 | India |
| 66 | Joe Root | 6120 | 893 | 10458 | 0 | 2 | 4 | 1 | England |
| 22 | Kane Williamson | 6174 | 2021 | 7368 | 2101 | 3 | 3 | 1 | New Zealand |
2. SQL INSERT INTO SELECT query
The SQL INSERT INTO SELECT query is used to add or copy data from an existing table into a newly created table. But there should be an equal number of columns and data types between the tables.
The syntax of the SQL INSERT INTO SELECT query is as follows:
INSERT INTO Table_Name_2 (SELECT * FROM Table_Name_1);
To insert into a new table from an old table, we will create a players table as shown below:
CREATE TABLE Players(Player_No int not null, Player_Name Varchar(100) not null, ODI_Runs int, T20_Runs int, Test_Runs int, IPL_Runs int, Best_ODI_Ranking int, Best_T20_Ranking int, Best_Test_Ranking int, Team varchar(100));
We will run the DESC query followed by the table name to check whether the Player table is successfully created or not:
DESC Players;
The output of the above query is as follows:
| Field | Type | Null | Key | Default | Extra |
| Player_No | int(11) | NO | NULL | ||
| Player_Name | varchar(100) | NO | NULL | ||
| ODI_Runs | int(11) | YES | NULL | ||
| T20_Runs | int(11) | YES | NULL | ||
| Test_Runs | int(11) | YES | NULL | ||
| IPL_Runs | int(11) | YES | NULL | ||
| Best_ODI_Ranking | int(11) | YES | NULL | ||
| Best_T20_Ranking | int(11) | YES | NULL | ||
| Best_Test_Ranking | int(11) | YES | NULL | ||
| Team | varchar(100) | YES | NULL |
We will not insert into the players table using the INSERT INTO SELECT query as shown below:
INSERT INTO Players (SELECT * FROM Player);
We will run the select query to check whether the data added successfully or not is as follows:
SELECT * FROM Players;
The output of the above query is as follows:
| Player_No | Player_Name | ODI_Runs | T20_Runs | Test_Runs | IPL_Runs | Best_ODI_Ranking | Best_T20_Ranking | Best_Test_Ranking | Team |
| 10 | Sachin Tendulkar | 18426 | 10 | 15921 | 2334 | 1 | 63 | 1 | India |
| 19 | Rahul Dravid | 10889 | 31 | 13288 | 2174 | 5 | 0 | 1 | India |
| 14 | Ricky Ponting | 13704 | 401 | 13378 | 91 | 1 | 1 | 1 | Australia |
| 18 | Virat Kohli | 12327 | 3308 | 8074 | 6624 | 1 | 1 | 1 | India |
| 45 | Rohit Sharma | 9359 | 3379 | 3137 | 5879 | 2 | 7 | 5 | India |
| 66 | Joe Root | 6120 | 893 | 10458 | 0 | 2 | 4 | 1 | England |
| 22 | Kane Williamson | 6174 | 2021 | 7368 | 2101 | 3 | 3 | 1 | New Zealand |