SQL INSERT Statement

In this tutorial, you will learn how to use the SQL INSERT Statement to insert records in a table with the help of examples.

The SQL INSERT statement is used to insert one or more records into a SQL table. This is one of the most basic and used SQL statements. 

There are two ways to insert data into a table:

1. By SQL insert into statement.
   1. By specifying column name.
   2. Without specifying the column name.
2. By using the INSERT INTO SELECT statement.

1) Inserting data using SQL INSERT INTO statement

You can insert data into a table using SQL INSERT INTO statement.

There are two ways to insert data into a table using the INSERT INTO statement.

In the first method, you do not need to specify the column name where the data will insert. You just need to specify the table name and the column values. Following is the syntax:

INSERT INTO table_name 
VALUES (value_1, value_2, value_3,....);

The second method specifies both the column names and values which you want to insert. 

INSERT INTO table_name (column_1,column_2,column_3,...)
VALUES (value_1, value_2, value_3,....);

Let’s take a simple example of the SQL INSERT statement. 

Suppose we have an employee table that is empty and we will insert few records into it.

CREATE TABLE Employee(
emp_id int NOT NULL,
first_name nvarchar(50) NOT NULL,
last_name nvarchar(50) NOT NULL,
dob date NOT NULL,
primary key(emp_id)
);

INSERT INTO Employee (emp_id,first_name,last_name,dob)
values (1,'Sagar','Sharma','1990-09-10');
INSERT INTO Employee (emp_id,first_name,last_name,dob)
values (2,'Ranjit','Bera','1991-02-07');
INSERT INTO Employee (emp_id,first_name,last_name,dob)
values (3,'Priya','Basak','1992-05-16');
INSERT INTO Employee (emp_id,first_name,last_name,dob)
values (4,'Rjau','Saha','1993-01-11');
INSERT INTO Employee (emp_id,first_name,last_name,dob)
values (5,'Minal','Ghosh','1992-09-23');

Now, if you run the select query on the table, you can see the following result.

mysql> select * from employee;
+--------+------------+-----------+------------+
| emp_id | first_name | last_name | dob        |
+--------+------------+-----------+------------+
|      1 | Sagar      | Sharma    | 1990-09-10 |
|      2 | Ranjit     | Bera      | 1991-02-07 |
|      3 | Priya      | Basak     | 1992-05-16 |
|      4 | Rjau       | Saha      | 1993-01-11 |
|      5 | Minal      | Ghosh     | 1992-09-23 |
+--------+------------+-----------+------------+
5 rows in set (0.06 sec)

You can insert a record in the Employee table by using below syntax as well.

INSERT INTO Employee values (6,'Ramu', 'Singh', 1998-05-25);

Now the table looks like as below.

mysql> select * from employee;
+--------+------------+-----------+------------+
| emp_id | first_name | last_name | dob        |
+--------+------------+-----------+------------+
|      1 | Sagar      | Sharma    | 1990-09-10 |
|      2 | Ranjit     | Bera      | 1991-02-07 |
|      3 | Priya      | Basak     | 1992-05-16 |
|      4 | Rjau       | Saha      | 1993-01-11 |
|      5 | Minal      | Ghosh     | 1992-09-23 |
|      6 | Ramu       | Singh     | 1998-05-25 |
+--------+------------+-----------+------------+
6 rows in set (0.07 sec)

2) Inserting data using INSERT INTO SELECT Statement

You can insert data records into the table using INSERT INTO SELECT Statement as well.

SQL INSERT INTO SELECT Syntax

INSERT INTO table_name
[(column_1, column_2,....., column_N)]
SELECT column_1, column_2,....., column_N
FROM table_name 
[WHERE condition];

Note:- While inserting data into the table using SQL insert into select statement, please keep in mind that the data types of the values and columns should match for both the tables.

SQL INSERT INTO SELECT example

Here, we will see how to insert data into the table from another table using insert into select statement.

Let’s create another table named Employee_tmp with the exact same definition.

CREATE TABLE Employee_tmp (
emp_id int NOT NULL,
first_name nvarchar(50) NOT NULL,
last_name nvarchar(50) NOT NULL,
dob date NOT NULL,
primary key(emp_id)
);

Now we will insert data into the new table Employee_tmp from the old Employee table using insert into select statement.

INSERT INTO Employee_tmp
SELECT * FROM Employee
WHERE emp_id<=5;

Check the content of the Employee_tmp table using the SELECT statement.

mysql> CREATE TABLE Employee_tmp (
-> emp_id int NOT NULL,
-> first_name nvarchar(50) NOT NULL,
-> last_name nvarchar(50) NOT NULL,
-> dob date NOT NULL,
-> primary key(emp_id)
-> );
Query OK, 0 rows affected, 2 warnings (0.17 sec)

mysql> INSERT INTO Employee_tmp
-> SELECT * FROM Employee
-> WHERE emp_id<=5;
Query OK, 5 rows affected (0.06 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM Employee_tmp;
+--------+------------+-----------+------------+
| emp_id | first_name | last_name | dob |
+--------+------------+-----------+------------+
| 1 | Sagar | Sharma | 1990-09-10 |
| 2 | Ranjit | Bera | 1991-02-07 |
| 3 | Priya | Basak | 1992-05-16 |
| 4 | Rjau | Saha | 1993-01-11 |
| 5 | Minal | Ghosh | 1992-09-23 |
+--------+------------+-----------+------------+
5 rows in set (0.00 sec)

In this tutorial, you have learned how to work with SQL INSERT statement and different ways to insert into SQL table.