Inserting Data in MySQL and MariaDB

In this guide, you'll learn how to insert data into your MySQL or MariDB databases, the insertion is similar for both MySQL and MariaDB RDBM (Relational Database Management System), so you are good.

To put data into the database, we use the INSERT command, and the synax is as follows:

INSERT INTO table [(column, …)]
  VALUES (value, …), (…), …;

The INSERT keyword is followed by the name of the table and an optional list of columns in parenthesis, the square bracket surrounding the column list is optional.

The VALUES keyword is followed by a pair of parenthesis containing a list of values for each column.

Let's see some examples:

Inserting Rows

To insert rows in a table, you need...

• The table names
• The list of columns you would be inserting data to
• and the data you would be insertiing

Here is an example:

INSERT INTO my_table_name (name,age) VALUES    
    ("Faruq","120");

So, the "my_table_name" is the table we are inserting data to, while (name, age) is the column we would be inserting the data, followed by the data we would be inserting which is Faruq and 120, simple right?

To insert multiple data you can separate them by a comma, for example:

INSERT INTO my_table_name (name,age) VALUES   
    ("Faruq","120"),   
    ("Adesewa","110"),   
    ("Omowunmi","10");

Inserting From Another Table

It so happens that sometimes, the data you want to insert into a table already exists in another table in the database, to copy the data from another table into the table you would be inserting the data, the INSERT command gives us a way to do that using the following SYNTAX:

INSERT INTO table_1 [(column, …)]
SELECT column_name[(column, …)]
FROM table_2;

Again, this is simple, it is like copy data from one table into another, to do that, you'll need:

• To gather the column and the table you'll be copying the data from

Suppose we want to copy the name and age from table 2 into table 1, you can do:

INSERT INTO table_1 (name_from_table_1, age_from_table_1)
SELECT name_from_table_2, age_from_table_2
FROM table_2;

Simple huh?

Note: You can use as many columns as you like as long as they exist in both tables, you would be fine.

Inserting From a File

A cool and faster way to insert data into a table is by reading it from a file, and the good this is that mariadb as well as mysql can do that natively by using the following syntax:

LOAD DATA [LOCAL] INFILE '<filename_path>'
INTO TABLE table_name [column_name, ....]

If the LOCAL option is used, mariadb would look for the file from the client, that is the computer that issued the query, otherwise, it would look for the file on the server the mariadb is running, that is the computer that is running the mariadb server engine.

The LOCAL would be implictly discarded if both the client and server are on the same computer.

If the column is not specifeid, MariaDB expects the data in the file to contain every column in the table we are inserting into, and they are in the same order.

Ensure you specify the column from the mariadb command to make things easier, let's see an example:

Supposer we have a file named "people" which has two coluns and corresponds to the "name" and "age" column in our peoples table, e.g

Faruq 120
Adesewas 110

To load the file into the peoples table, we do:

LOAD DATA INFILE '/path/to/peoples_file'
INTO TABLE peoples_table (name, age);

You can use the "terminated by" keyword if the data in the file is not separated by a tab, for example if it is separated by a comma(,):

Faruq, 120
Adesewas, 110

You can insert the data in the following way:

LOAD DATA INFILE '/path/to/peoples_file'
INTO TABLE peoples_table terminated by ',' (name, age);