This tutorial explains the MySQL INSERT command to insert single and multiple rows in a table. Here, you’ll find some unique ways with different variations for adding records with fully working examples.
1. INSERT Statement Syntax
2. INSERT Single Row
3. INSERT Default Values
3. INSERT Date Columns
4. INSERT Multiple Rows
Let’s now read and understand each of the sections one by one.
MySQL INSERT statement
As stated initially, the INSERT command is a built-in MySQL statement that inserts the specified records into a given table. So, let’s first check the details and see how to use the INSERT command. But first, create a sample table which we’ll use in our examples.
-- Creating a sample table we will use in our examples
CREATE TABLE EMPL
(
e_id INT AUTO_INCREMENT PRIMARY KEY,
e_first_name VARCHAR(20) NOT NULL,
e_middle_name VARCHAR(20),
e_last_name VARCHAR(20),
e_address VARCHAR(40),
e_pin VARCHAR(10),
e_salary INT NOT NULL DEFAULT 5000,
e_birth_date DATE,
e_join_date DATE
);
Syntax
Below is the signature of this command:
-- Adding a single record using INSERT command INSERT INTO TABLE (field1_name, field2_name, ...) VALUES (value1, value2, ...);
Below are some facts about the above statement.
- You begin this MySQL query with the INSERT INTO clause. Then, you provide the table name appended with a comma-separated list of all its fields inside parentheses.
- After that, the keyword VALUES appears with a list of comma-delimited values (one value per field) in parentheses.
While supplying the VALUES, you need to make sure that each field has a corresponding value. Moreover, the order of field values should also align.
By the way, it is common that you may want to insert multiple rows in a table with one INSERT statement. It has a little modified syntax:
-- Adding multiple records with single INSERT command
INSERT INTO TABLE
(field1_name, field2_name, ...)
VALUES
(value11, value12, ...), -- row 1
(value21, value22, ...), -- row 2
...
(valueN1, valueN2, ...); -- row N
MySQL INSERT Statement Examples
We already have created a sample table for practicing with the INSET command. Let’s now utilize it in our examples:
Simple Insert Query Example
The following MySQL query inserts a new row to the EMPL table:
-- Inserting a single row in EMPL table
INSERT INTO
EMPL(e_first_name, e_salary)
VALUES
('Amit', 20000);
In our first example, we only mentioned the values for two fields: e_first_name and e_salary. For the rest of the columns, MySQL would assume the default values.
You can confirm the same after running this query.
SELECT * FROM EMPL;
The result should come like this:
e_id, e_first_name, e_middle_name, e_last_name, e_address, e_pin, e_salary, e_birth_date, e_join_date 1 , Amit , NULL , NULL , NULL , NULL , 20000 , NULL , NULL
All the fields except e_id, e_first_name, and e_salary have initialized with NULL as the default value. It is because MySQL inserted NULL for remaining columns which didn’t appear in the INSERT command.
Also, note that we only inserted one field which e_first_name. The other one, e_id is of auto-increment type. Hence, MySQL auto-generated a value for the same.
Insert with Default Values
We can also supply the default values explicitly. You have seen the implicit way (by ignoring the columns in the INSERT) in the previous example.
It is simple as we only have to place the DEFAULT keyword in the INSERT INTO clause for every corresponding field name.
Check out the following MySQL example, which illustrates how to insert default values:
-- Inserting default values in EMPL table
INSERT INTO
EMPL (e_first_name, e_salary)
VALUES
('Vijay', DEFAULT);
Here, we’ve specified two columns and provided their specified values. For instance – DEFAULT for the e_salary field.
It is because of the e_salary column, which has a default value of 5000, as mentioned in the CREATE command.
e_salary INT NOT NULL DEFAULT 5000
Let’s now fetch the records from the EMPL table:
SELECT * FROM EMPL;
After querying the contents of the EMPL table, the following result comes:
e_id, e_first_name, e_middle_name, e_last_name, e_address, e_pin, e_salary, e_birth_date, e_join_date 1 , Vijay , NULL , NULL , NULL , NULL , 5000 , NULL , NULL
Fill Date Values using Insert
We can fill the date type fields by inserting the values in the following style:
'YYYY-MM-DD' or 'YYYY/MM/DD'
This convention implies:
- YYYY -> Four-digit year, for example, 2019.
- MM -> Two-digit month, for example, 01-12.
- DD -> Two-digit day, for example, 01-28 or 01-30 or 01-31.
The below example inserts a new record to the EMPL table with birth and join_date values:
-- Inserting date type values in EMPL table
INSERT INTO
EMPL (e_first_name, e_birth_date, e_join_date)
VALUES
('Sojay', '2000-02-11','2019-07-15');
The following output captures the current state of the EMPL table post the insert:
After querying the contents of the EMPL table, the following result comes:
e_id, e_first_name, e_middle_name, e_last_name, e_address, e_pin, e_salary, e_birth_date, e_join_date 1 , Sojay , NULL , NULL , NULL , NULL , 5000 , 2000-02-11 , 2019-07-15
Insert Multiple Rows in EMPL Table
So far, in our examples, we have applied the MySQL INSERT statement to add a single record. However, it is possible to push multiple rows using the INSERT command.
Check the following statement that inserts four rows into the EMPL table:
-- Inserting multiple rows in EMPL table
INSERT INTO EMPL
(
e_first_name, e_middle_name, e_last_name, e_address, e_pin, e_salary, e_birth_date, e_join_date
)
VALUES
('Amit', 'Kumar', "Singh", "Sec-62, Noida", "201301", DEFAULT, '2000-02-11','2019-08-28'),
('Jyoti', 'Rani', "Saini", "Sec-21, Noida", "201301", 10000, '1998-03-01','2019-07-30'),
('Vimal', 'Kumar', "Sood", "Sec-11, Noida", "201301", DEFAULT, '1997-08-09','2019-03-14'),
('Neelam', 'Jai', "Singh", "Sec-23, Noida", "201301", 50000, '2001-01-12','2019-04-21');
SELECT * FROM EMPL;
The result is as follows:
1 Amit Kumar Singh Sec-62, Noida 201301 5000 2000-02-11 2019-08-28 2 Jyoti Rani Saini Sec-21, Noida 201301 10000 1998-03-01 2019-07-30 3 Vimal Kumar Sood Sec-11, Noida 201301 5000 1997-08-09 2019-03-14 4 Neelam Jai Singh Sec-23, Noida 201301 50000 2001-01-12 2019-04-21
We hope that after wrapping up this tutorial, you should feel comfortable using the MySQL INSERT statement. However, you may practice more with examples to gain confidence.
Also, to learn SQL from scratch to depth, read our step-by-step MySQL tutorial.
