Insert records in a MySQL database table

Inserting records into a MySQL table is a very common action in database operation.

Insert records in MySQL
Insert records in MySQL

The insertion of records allows the entry of data into a table, either a single vector of values, several rows at a time, or one or more rows from the query to another table or tables.

So we are going to instruct in a simple way how to insert records. We will use a table called employees as an example:

idnamelastnameperson_id
1JuanPardo123
2LuisCadavid456
3MaríaMora899
Employees table

Insert a single record

If we want to insert a single record, we must use the following syntax:

INSERT INTO <table name>(field1,field2,...,field-n) values(value1,value2,...,value-n);

Where the number of fields must match the number of values, and they must be of the same data type.

You can skip the list of fields in the command, if you insert as values all the fields of the table, in the same order in which they are listed in its design.

In our example, we can insert a record in the employees table as follows:

INSERT INTO employees(id,name,lastname,person_id) values(4,"Jose","Acevedo","7491"); 

Insert multiple records

In the case of multiple records, the following syntax should be used:

INSERT INTO <table name>(field1,field2,...,field-n) values
(value11,value12,...,value-1n),
(value21,value22,...,value-2n),
...
(value-n1,value-n2,...,value-nn);

In our example, the insertion of several records would be as:

INSERT INTO employees(id,name,lastname,person_id) values
(5,"Camilo","Montoya","74997"),
(6,"Joaquin","Carrizales","8538"),  
(7,"Maritza","Miranda","6384");  

Assuming that the id field is a unique auto-incremental key, it can be ignored in both the field list and the value list. MySQL will automatically enter the next value in the sequence.

If a field is not named in the insert, the command tries to insert the default value defined for the field in the table layout. For example, a field may default to a NULL or empty value when no explicit value is set for that field in the insert. If a field does not allow a null value, then an error will be generated on insertion if a value other than null is not set for that field.

Inserting records from a MySQL Query

In this case the insertion does not depend on fixed values, as in the previous cases, but the values are returned from a query, which is performed to another table, or set of tables.

As in the case of the insertion of fixed values, the number of fields to be inserted must match the number of values returned by the query, otherwise the insertion will fail. So the following syntax must be followed:

INSERT INTO <table name>(field1,field2,...,field-n) SELECT field11,field12,...,field1n) FROM <table name 2> WHERE <conditional>;

For our example, let’s consider another table called operators:

idnamelastnamepersonidposition
1LuisBola√Īos887Auxiliar
2MarthaCaviedes9781Documentación
3MiguelCasta√Īo835Auxiliar
Operators table

Let’s suppose that we want to insert in the table employees, the records of the table operators that have as position ¬ęAuxiliary¬Ľ.

As the employee id field is auto-incremental, we should not include the operator id in the query:

INSERT INTO employees(name,lastname,person_id) SELECT name,lastname,personid FROM operators WHERE job title="Auxiliary";

This inserts the first and third records of the operators table into the employees table.

Restrictions on record insertion

Since tables may have restrictions according to their design, the insertion may fail in any of the following cases:

1- Because of the data type of the field: the value to be inserted must be of the same type as the field, or an automatic type conversion must be possible. For example, if we have an integer type field, it will not be possible to insert a text value.

2- Table keys: Keys allow the creation of indexes, control unique values and maintain the referential consistency of the database. These keys can define that a field can only have different values, or that the combination of fields must be unique, or that a field is a foreign key, which indicates that this field must contain a value, which must exist as a unique key in another table, in order to have a referential integrity of the data.

3- Null or empty fields: Depending on the definition of the table fields, they may or may not allow a null value by default, which must be considered at the time of assembling the data insertion.

In another article, we will be talking in more detail about the importance of the keys of a table in MySQL.

This article is part of ITSoftware’s knowledge diffusion system.