SQL INSERT INTO – Data Insertion in database8 min read

The SQL INSERT command is used to insert data into a table in a database (such as a MySQL or PostgreSQL database for example).
Inserting data into the database with SQL INSERT… INTO is one of the basic and most common operations that we will perform when handling data from databases, just like selecting with the SQL SELECT command. or updating data with SQL UPDATE.

SQL INSERT will allow us to add one or more entries at once to a database.

SQL INSERT syntax

The SQL INSERT command will allow us to insert new data (entries) into a table. We will be able to specify only the values ​​relating to certain columns or the values ​​for each column during the insertion. We will also be able to choose to insert one or more entries at once in our SQL table with INSERT.

Insert an entry specifying values ​​for each column
In most cases, we will use INSERT to insert a new entry into a database table, specifying values ​​for each column in the table.

In this case, we will be able to use the INSERT INTO syntax table_name VALUES ‘value 1’, ‘value 2’,…).

This command literally means “inserts into the table“ table_name ”the values“ value1 ”,“ value2 ”, etc.

When specifying values ​​for each column in the table, there is no ambiguity about the values, so there is no need to specify the column names with each value.

SQL will place the values ​​in the columns in the order of their writing (the first value will go in the first column available for writing, the second value in the second column, etc.).

Note that if your table has an id type column with auto_increment and a default value of not null, then we can simply specify 0 or null in the value of this column and the value will automatically be increments correctly.

Insert an entry by specifying values ​​for certain columns only
We will also be able to use INSERT to insert new entries by specifying the values ​​to be inserted only for certain columns and leaving the others empty.

In this case, it will of course be necessary for the columns left empty to have a default value other than none and it will also be necessary to indicate to SQL to which column each value corresponds, that is to say in which column each value must be stored.

So here we will also have to specify the names of the columns relating to each data and for this we will use the following syntax:

INSERT INTO table_name (column_name1, column_name2,…) VALUES (‘value 1’, ‘value 2’,…).

You can note here that there is no need to surround the column names with apostrophes or single quotes.

Note also that if your table has an id column in primary key with an auto increment option, you can omit the value which will be automatically filled by SQL.

Insert multiple entries with SQL INSERT

Finally, we will also be able to use the SQL SELECT command to insert several entries at the same time. To do this, we will separate the values ​​of the different entries with a comma.

Here, we will once again be able to choose to specify the values ​​for each column of the table or to specify the values ​​only for certain columns. The only rule to respect is that each new entry inserted must be in the same form as the others, that is to say that the data specified must correspond to the same columns as the previous ones.

We will therefore not be able to insert the data relating to the first three columns of our table for an entry, then the data relating to columns 1, 4 and 5 for another entry at once, for example.

To insert several entries by specifying values ​​for each column, we will use the following syntax: INSERT INTO table_name VALUES (‘value 1.1’, ‘value 1.2’,…), (‘value 2.1’, ‘value 2.2’,…) , (‘value 3.1’, ‘value 3.2’,…).

If you only want to specify the values ​​for certain columns, you will have to indicate the column names as before:

INSERT INTO table_name (column_name1, column_name2,…) VALUES (‘value 1.1’, ‘value 1.2’,…), (‘value 2.1 ‘,’ value 2.2 ‘,…), (‘ value 3.1 ‘,’ value 3.2 ‘,…).

Examples of using SQL INSERT to insert data into a MySQL table

To test the SQL INSERT command, we’re going to need a database with at least one table.

There are different methods of creating a database and a table. The two most common are using phpMyAdmin or a PHP script with a mysql or PDO extension.

Here we will create a MySQL course database containing a users table. I therefore invite you to start your servers (using MAMP, WAMP, etc.) and go to your phpMyAdmin (accessible from the start page of WAMP, MAMP…).

Then create the course base by going to the “database” tab and then in the “create a base” field. Finally select the base by clicking on it then go to the “import” tab to import the users table that I have prepared for you and which you can download by clicking here.

This users table contains 6 columns and eight fields as in the image below:

Data insertion via phpMyAdmin

Let’s try to insert new entries into our table by typing our SQL commands directly in the “SQL” tab of phpMyAdmin.

We will already start by inserting a single entry specifying the data for each column of our table. As our first column is an id column with auto_increment, we can specify 0 or null so that MySQL automatically defines an incremented value compared to the previous one.

Here I also use the CURRENT_TIMESTAMP function for the registration date field so that the date corresponds to the exact date when the entry was inserted.

Now try to insert several entries at the same time by specifying the values of only certain columns. For our query to work normally, the omitted columns must have a default value other than none. I therefore invite you to go to the “structure” tab of your table and modify the default value of the “age” and “mail” columns by indicating NULL rather than none.

Then let’s try to insert two entries by specifying only the values of the columns “f_name”, “l_name” and “age”:

As you can see, the values in the “id” column are always automatically increments while the values in the “date” column are by default equal to CURRENT_TIMESTAMP. The values of “mail” are NULL by default.

Data insertion with PHP and PDO extension

We will often want to dynamically insert data into the database, such as when a customer provides us with registration information. To achieve this, we will not be able to go through phpMyAdmin but will rather have to use a server scripting language like PHP.

To communicate with databases, we will be able to use one of the two PHP extensions mysql or PDO. In this tutorial, I will use PDO.

Let’s start by creating an insert.php file. This file will contain the PHP code to connect to the course database and to insert the data into our users table.

To insert the data, we will use the prepared queries here and therefore the prepare () and execute () functions. We will use named markers and pass an array with predefined values to execute () for simplicity.

This script connects to the course database and adds an entry to the users table with the following values:

The main difficulty for me here is to provide a value in the right format for the column “date_inscription” of our table.

We do this by using the PHP date () function to format the date in the correct format and passing it the current date with time () to which I add 2 hours so that it corresponds to the configuration of my database.

If we only want to specify the values for certain columns, we will have to specify the column names again in our SQL query. If you want to insert several entries at once, you can use a PHP loop but the simplest way is to use several insertion requests, each request inserting a new entry.