SQL INSERT Statement

The SQL INSERT statement is used to insert one or multiple records into a table.

To insert text and date values, you need to enclose them using single quotes.

Syntax

There are two syntaxes for the INSERT statement depending on whether you want to insert all the columns or just specific columns.

The first syntax specifies both the column names and the values to be inserted.

INSERT INTO table_name(column1_name, column2_name, ..., columnN_name)
VALUES (value1, value2, ..., valueN);

If you want to add values for all the table columns, you do not need to specify the column names in the SQL statement. However, be sure the values' order is in the same order as the columns in the table. The second INSERT statement syntax would be as follows:

INSERT INTO table_name
VALUES (value1, value2, ..., valueN);

Demo Table

Let us suppose that we have a table called "employee" with the following records:

INSERT INTO Example

The following SQL statement will insert a new record in the "Employee" table:

INSERT INTO employee (first_name, last_name, birth_date, salary, dept_name)
VALUES ('Donna', 'Williams', '1996-03-15', '2012-06-06', 7400, 'Finance');

After running the above SQL statement, the selection from the "employee" table will now look like the following:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 1001   | James       | Smith     | 1991-10-23 | 2010-05-13 | 6600      | Customer Service |
| 1002   | Maria       | Martinez  | 1993-06-13 | 2018-10-08 | 9000      | Development      |
| 1003   | Andrew      | Rodriguez | 1994-10-25 | 2019-01-23 | 9500      | Finance          |
| 1004   | Robert      | Hernandez | 1995-09-07 | 2019-09-08 | 9600      | Development      |
| 1005   | Donna       | Williams  | 1996-03-15 | 2012-06-06 | 7400      | Finance          |
+--------+-------------+-----------+------------+------------+-----------+------------------+

As you can see, we didn't insert any value for the "emp_no" column because the "emp_no" was created with the AUTO_INCREMENT flag, which makes the database system automatically assign a value when it is left unspecified, you can see more in the create table chapter.

Note: Quotes must enclose non-numeric values like Text and Dates. On the other hand, numeric values must never be enclosed using quotes. In the case that the Text that you want to insert contains quotes, you can escape it with a backslash like 'Let's start with our learning'.

INSERT Data in Specified Columns

The INSERT statement allows the insertion of data in specific columns.

The following SQM statement will insert a new record in the "employee" table, but only the data of "first_name", "last_name", and "salary" columns will be added.

INSERT INTO employee (first_name, last_name, salary)
VALUES ('James', 'Johnson', 8000);

After inserting the new record, the selection from the "employee" table will now look like this:

+--------+-------------+-----------+------------+------------+-----------+------------------+
| emp_no | first_name  | last_name | birth_date | hire_date  | salary    | dept_name        |
+--------+-------------+-----------+------------+------------+-----------+------------------+
| 1001   | James       | Smith     | 1991-10-23 | 2010-05-13 | 6600      | Customer Service |
| 1002   | Maria       | Martinez  | 1993-06-13 | 2018-10-08 | 9000      | Development      |
| 1003   | Andrew      | Rodriguez | 1994-10-25 | 2019-01-23 | 9500      | Finance          |
| 1004   | Robert      | Hernandez | 1995-09-07 | 2019-09-08 | 9600      | Development      |
| 1005   | Donna       | Williams  | 1996-03-15 | 2012-06-06 | 7400      | Finance          |
| 1006   | James       | Johnson   | NULL       | NULL       | 8000      | NULL             |
+--------+-------------+-----------+------------+------------+-----------+------------------+