1. Introduction
In this article, we will look at one of the Data Manipulation Language (DML) constructs or statements called Insert query using the MySQL RDBMS.
2. What is Data Manipulation Language?
In SQL, Data Manipulation language consists of SQL data-change statements that modify data but not the underlying schema or database objects. The commands included in the data manipulation language are:
- Insert – add new records/tuples in table or tables.
- Update – alter records/tuples already present.
- Delete – remove records/tuples.
Unlike Data Definition statements, DML statements are not auto-commit transactions by default. Hence, until we specifically “commit” the transaction, the changes can be undone. These commands also correspond to the “CRUD” (Create-retrieve-update-delete) statements which most front-end applications use.
2.1 What is a Transaction?
A transaction is a unit of work, performed in the order in a database. Transactions propagate or reverse one or more changes done to a database. Transactions are used for DML statements and are extremely important to ensure data integrity. More on transactions in general here.
3. SQL Insert Query Syntax
In general, the Insert statement has 2 syntaxes. The first syntax is to insert only specific columns in a table.
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
For this insert statement syntax, all the columns with the “Not NULL” constraint must be inserted. The columns which are not inserted, get the value of “NULL”. Alternatively, we can also specify a default value to be specified by using the DEFAULT Keyword.
If all the columns in the table are to be filled, we can simply skip the column_names and directly give the values.
INSERT INTO table_name VALUES (value1, value2, value3, ...);
The syntaxes above will insert a single record in the table mentioned. To insert multiple records into a table, depending on the RDBMS we use, different syntaxes are available. For example, to insert multiple records at once in Oracle, we use the “INSERT ALL” while the MySQL and PostgreSQL use the “INSERT INTO”. Microsoft SQL does not support multiple record insertion
4. INSERT Query in MySQL
The full syntax of the insert query as per the MySQL docs is
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[(col_name [, col_name] ...)]
{ {VALUES | VALUE} (value_list) [, (value_list)] ...
|
VALUES row_constructor_list
}
[AS row_alias[(col_alias [, col_alias] ...)]]
[ON DUPLICATE KEY UPDATE assignment_list]
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[AS row_alias[(col_alias [, col_alias] ...)]]
SET assignment_list
[ON DUPLICATE KEY UPDATE assignment_list]
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[(col_name [, col_name] ...)]
[AS row_alias[(col_alias [, col_alias] ...)]]
{SELECT ... | TABLE table_name}
[ON DUPLICATE KEY UPDATE assignment_list]
value:
{expr | DEFAULT}
value_list:
value [, value] ...
row_constructor_list:
ROW(value_list)[, ROW(value_list)][, ...]
assignment:
col_name = [row_alias.]value
assignment_list:
assignment [, assignment] ...
To understand the most used options and their syntaxes, we will look at some examples next.
4.1 Setup for examples
Forgoing through the examples related to Insert, we will consider a new database called “TestDB”. This database has 3 tables named employees, departments, and employee_dept.
The Create scripts for the 3 tables are as follows:
CREATE TABLE EMPLOYEES(
EMP_ID INT AUTO_INCREMENT PRIMARY KEY,
FIRST_NAME VARCHAR(20) NOT NULL,
LAST_NAME VARCHAR(20),
BIRTH_DATE DATE DEFAULT '1900-00-00',
HIRE_DATE DATE DEFAULT (CURRENT_DATE())
);
CREATE TABLE DEPARTMENTS(
DEPT_ID INT AUTO_INCREMENT PRIMARY KEY,
DEPT_NAME VARCHAR(30)
);
CREATE TABLE EMPLOYEE_DEPT(
RECORD_ID INT AUTO_INCREMENT PRIMARY KEY,
EMP_ID INT,
DEPT_ID INT,
FROM_DATE DATE,
TO_DATE DATE,
FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEES(EMP_ID),
FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENTS(DEPT_ID)
);
We also have another table called EMPLOYEES2 which has the same structure as the Employees table.
CREATE TABLE EMPLOYEES2(
EMP_ID INT PRIMARY KEY,
FIRST_NAME VARCHAR(20) NOT NULL,
LAST_NAME VARCHAR(20),
BIRTH_DATE DATE DEFAULT '1900-00-00',
HIRE_DATE DATE DEFAULT (CURRENT_DATE())
);
For running the queries, we will use the MySQL Workbench. The documentation for MySQL Workbench is available here. Set the Workbench to “Auto-commit Transactions”.
5. INSERT INTO Examples
Using the setup above we will look at various options and syntaxes used with the Insert statements.
5.1 Insert a single row in the table using column_names
To insert a single row in say the Employees table, we use the query
INSERT INTO EMPLOYEES (FIRST_NAME, LAST_NAME, BIRTH_DATE, HIRE_DATE)
values('Thorin','Oakenshield','2746-01-01','2760-05-01');
The output is as follows:
If you observe, the first column in the employees table: EMP_ID is not mentioned in our Insertion Query but is still inserted with a correct value. The reason for that is we have declared the EMP_ID as an auto_increment value. It means that MySQL generates a sequential integer whenever a row is inserted into the table.
5.2 Insert a single row in a table without column_names
If all the columns are to be inserted in a table, we can skip the column names altogether and just put the values. While doing so, we must set the EMP_ID i.e. the auto_incremented value to a DEFAULT value.
INSERT INTO EMPLOYEES values(default,'Thrain','Oakenshield','2850-01-01','2910-05-01');
The output is as follows:
5.3 Insert a single row in a table with a default value
In the employees table, we have defined the Birth_date with the default value as “1900-00-00”. When during insertion we do not mention the birth_date, then the record is inserted with the default value.
INSERT INTO EMPLOYEES (FIRST_NAME,HIRE_DATE)
values('Smaug','2600-05-01');
The output is as follows:
5.4 Insert with Not NULL constraint violation
If a column is specified as “Not NULL”, it necessarily needs to have a value inserted in it. If we try to insert a record without a value specified for the “Not NULL” column, then insertion fails and the record is not inserted.
INSERT INTO EMPLOYEES(LAST_NAME,BIRTH_DATE,HIRE_DATE)
VALUES('Oakenshield','2650-09-12','3010-07-15'); The output is as follows:
5.5 Insert Multiple records in a table
To insert multiple records in the Departments table, we use the query
INSERT INTO DEPARTMENTS (DEPT_NAME)
values
('Logistics'),
('Support'),
('Development'),
('Transport');
Here again, the DEPT_ID column is auto-increment and will be generated in Sequence.
5.6 Insert into table with Foreign keys constraint
For Insertion in a table, with references to other tables i.e. foreign keys, the records for the Foreign key must exist in the other table before we can insert into the current table.
Correct Foreign key reference query
INSERT INTO EMPLOYEE_DEPT (EMP_ID,DEPT_ID,FROM_DATE,TO_DATE) VALUES (1,3,'2760-05-15','2800-10-15');
The output is as follows:
Here both the records for the EMP_ID and DEPT_ID exist in their respective tables.
5.7 Foreign Key constraint violation
IF the Foreign key we are trying to insert is either not present or is incorrect, then record cannot be inserted
INSERT INTO EMPLOYEE_DEPT (EMP_ID,DEPT_ID,FROM_DATE,TO_DATE) VALUES (3,10,'2765-05-15','2900-10-15');
The output is as follows:
Here the record for the DEPT_ID used is not present in the Departments table.
5.8 Insert Into using a Select Query
If we have two identical tables or even one table which has a subset of columns from the other table, we can use the select query with the insert query.
To select a particular record to insert
INSERT INTO EMPLOYEES2 (EMP_ID, FIRST_NAME, LAST_NAME, BIRTH_DATE, HIRE_DATE) SELECT EMP_ID, FIRST_NAME, LAST_NAME, BIRTH_DATE, HIRE_DATE FROM EMPLOYEES WHERE EMP_ID =1;
If the Columns names in the two tables are identical then we can skip the column names in the Select query and use the asterisk (*) instead.
INSERT INTO EMPLOYEES2 (EMP_ID, FIRST_NAME, LAST_NAME, BIRTH_DATE, HIRE_DATE) SELECT * FROM EMPLOYEES WHERE EMP_ID =2;
The queries above give the output:
5.9 Insert into using a Select without a where clause
To insert all the records from one table to another , we can use any one of the syntaxes of the select statement from above, just without a where clause
INSERT INTO EMPLOYEES2 (EMP_ID,FIRST_NAME,LAST_NAME,BIRTH_DATE,HIRE_DATE) SELECT * FROM EMPLOYEES;
The output is:
5.10 Insert into with the ON DUPLICATE KEY UPDATE
If we try to insert a row in a table with a primary key that already exists, we get a “Duplicate entry” error. This would apply to a column that has a “Unique” constraint as well.
INSERT INTO EMPLOYEES2 (EMP_ID,FIRST_NAME) VALUES (4,'Bofur');
The error shown is
However, if we use the On DUPLICATE KEY UPDATE statement with the insertion, then the record already present in the table is updated instead. Example Query
INSERT INTO EMPLOYEES2 (EMP_ID, FIRST_NAME) VALUES (4,'Smaug') ON DUPLICATE KEY UPDATE LAST_NAME="The Fierce";
The Emp_id = 4 already exists in the Employees2 table. When we mentioned, the on Duplicate key update statement, the record for the emp_id=4 is retrieved and updated i.e. the Last_Name is updated from NULL to “The Fierce” in the above example.
6. Summary
In the article, we saw the most common options used with the INSERT INTO query in MySQL. The complete documentation of the INSERT INTO Clause is available here.
7. Download the Source Code
In this article, we saw examples of the Insert into Query using my MySQL RDBMS. Attached are all the queries and also the creation scripts used.
You can download the full source code of this example here: SQL Insert Query Example
Last updated on Sept. 29th, 2021
