MySQL Insert Into Statement
To insert a record in the table, we use a MySQL insert statement. The basic syntax of an insert statement is as follows:
If we want to insert values for all the columns in the table, we can skip writing out the column-names.
MySQL Insert Into Syntax
INSERT INTO table_name
VALUES (value1, value2, value3, ...);
In case we want to insert a record with only specific columns, we can do:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
In this case, the rest of the columns, other than the ones specified, are filled with NULL values provided NULL values are allowed. In case we have specified Default values then those are inserted.
We also have scenarios in which we can dump another table's data into the current table or we can insert multiple values at once. We will see these syntaxes through examples. For the examples, we will be using the MySQL Workbench. The procedure for inserting records using the command-line client is the same. There are no differences in the Syntax of the two methods.
MySQL Insert into a statement using specific columns names
Here we specify the names of the columns we want to insert values for.
Example:
INSERT INTO EMPLOYEES (FIRST_NAME, LAST_NAME, BIRTH_DATE, HIRE_DATE)
values('Thorin','Oakenshield','2746-01-01','2760-05-01');
MySQL insert into statement insertion error while inserting records with specific columns
When we insert records for specific columns, we should take care that all the columns we do not mention either have a default value defined or allow NULL values to be inserted in them. For example, in the Employees table, the First_Name column has a Not NULL condition on it. If we miss adding a value in the First_name column, the insertion will fail.
INSERT INTO EMPLOYEES (LAST_NAME, BIRTH_DATE, HIRE_DATE)
values('Oakenshield',default,default);
MYSQL insert into a statement for all the columns
When we want to insert a value in each of the records, we can skip the column names altogether. In this scenario, for the columns that have default values, we can either put a value of our choice or the keyword default.
This is an example of the error that MySQL triggers when all the columns are not correctly mentioned.
insert into employees
values('Arwen','Noldor','3006-03-15','3016-05-01');
To correct this error, the correct syntax is:
insert into employees
values(default,'Arwen','Noldor','3006-03-15','3016-05-01');
MySQL Insert into statements to insert multiple records in a table
To insert multiple records into a table we mention the keyword Values only for the first record. all the records(rows) we want to insert are comma-separated values. We can either insert all the columns of the table or some columns of the table as required.
Example 1: Inserting multiple values in a table using the list of a specific column.
INSERT INTO DEPARTMENTS (DEPT_NAME)
values
('Logistics'),
('Support'),
('Development'),
('Transport');
The other column dept_id is auto-incremented and inserted.
Example 2: Inserting multiple values using all the columns in the table.
INSERT INTO EMPLOYEES
values
(default,'Figwit','Baggins','3010-02-01','3020-05-01'),
(default,'Haldir','Lothlorien','3010-03-01','3020-07-01'),
(default,'Arwen','Noldor','3006-03-15','3016-05-01'),
(default,'Gil-Galad','Noldor','2800-05-30','2820-10-01'),
(default,'Tauriel','',DEFAULT,'3020-05-01'),
(default,'Glorfindel','Ringwraith','2810-02-01',default),
(default,'Celeborn','Silmarillion','3010-02-01','3020-05-01'),
(default,'Legolas','Mirkwood','3010-02-01','3020-05-01'),
(default,'Celebrimbor','Sauron','3010-02-01','3020-05-01'),
(default,'Thranduil','Mirkwood','3010-02-01','3020-05-01'),
(default,'Balin','Durin','2763-02-01','2800-01-01'),
(default,'Dwalin','Fundin','2672-02-01','2800-02-01'),
(default,'Bifur','Baldin','2770-05-20','2800-03-01'),
(default,'Bofur','Belegost','2780-12-01','2800-04-01'),
(default,'Bombur','Erebor','2781-02-01','2800-05-01');
MySQL Insert into a statement for inserting in a table with Foreign keys
If we want to insert a record into a table with reference to another table i.e. foreign key, the foreign key we are inserting needs to be present in the parent table.
Example 1: Foreign key does not exist
INSERT INTO EMPLOYEE_DEPT (EMP_ID,DEPT_ID,FROM_DATE,TO_DATE)
VALUES (3,10,'2765-05-15','2900-10-15');
Example 2: Foreign key exists
INSERT INTO EMPLOYEE_DEPT (EMP_ID,DEPT_ID,FROM_DATE,TO_DATE)
VALUES (1,3,'2760-05-15','2800-10-15');
MySQL Insert into a statement with Select and Where Query
Some we need to pick record(s) from one table and insert it into another table. Instead of building the data again, we can simply insert from one table to another using the select and where query.
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;
Instead of mentioning all the columns in the select query, we can use a * as well.
INSERT INTO EMPLOYEES2 (EMP_ID, FIRST_NAME, LAST_NAME, BIRTH_DATE, HIRE_DATE)
SELECT * FROM EMPLOYEES WHERE EMP_ID =2;
MySQL insert into a statement with Select * query:
When the want to dump the contents of one table into another we use a select all(*) query to do so. This is particularly useful when we want to migrate a database.
INSERT INTO EMPLOYEES2 (EMP_ID,FIRST_NAME,LAST_NAME,BIRTH_DATE,HIRE_DATE)
SELECT * FROM EMPLOYEES;
MySQL insert into with on DUPLICATE KEY UPDATE
If we try to insert a duplicate record for a Primary key, MySQL throws an error.
INSERT INTO EMPLOYEES2 (EMP_ID,FIRST_NAME)
VALUES (4,'Bofur');
To work around the error, we use the On Duplicate key update statement.
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 and so when we mentioned, the on Duplicate key update statement, the record for the emp_id=4 is updated i.e. the Last_Name is updated from NULL to “The Fierce” in the above example.
