Inserting data

Now that we have built our tables, it is time to insert records into the tables. Let us look at a few different methods for inserting a single row of data and inserting multiple rows of data. For insertion of data into a table, we will use the INSERT DML command, and supply the table name and the values for the available columns in the table. Let us begin by inserting student records into the students table:

In this example, we insert a new student record into the students table; we are supplying the data for that student record in the VALUES clause. This syntax, though it appears to be very simple, it is not a very safe method of inserting data. This INSERT statement is depending upon the order in which the columns were defined in the table structure, so the data in the VALUES clause will be mapped by position, 1 would go into the first column in the table, though it is intended to go into the student_id column. If the students table is rebuilt locally or on a different machine, there is no guarantee that the column order would remain the same as the order on the current MariaDB database server. The other approach that is considered safer when compared to this one is the INSERT statement, where the column names are explicitly mentioned in the SQL:

Though this might be a bit longer, this would guarantee that data that is being passed in via the VALUES clause is going into the right column. By using this INSERT syntax, the order in which the columns are mentioned is no longer important. When this query is executed, MariaDB matches each item in the columns list with its respective value in the VALUES list by position. This syntax can also be used for the case where the data is only available for a few columns. Let us come up with an INSERT statement that has data for a few columns and uses NULL for a column that does not have any data:

In this example, we are inserting a student record whose address is not known, so we are using NULL to populate the column.

Now that we have seen the different insertion syntaxes for inserting a single record row, let us take a step forward and look at how multiple records can be inserted. There are two ways of inserting multiple records into a table, the first method is where INSERT statements are created for each row, and are separated by the statement terminator (;):

The other way of inserting multiple records is by using a single VALUES clause while passing in multiple records, separating each record with a comma (,), and adding a statement terminator at the end of the last record:

In this example, we are inserting multiple records into the students_courses table. On execution of this SQL query, the first statement inserts an associative record into the students_courses table and the value for the column student_id is 1, which maps back to the student record of John Doe, and the value for course_id is 1 that corresponds to the course record CS-101. The inline comments at the end of each statement are used to describe the data that is being inserted via this statement. Though these comments are added to the INSERT statements, they are only intended to explain the purpose of the statements and will not be processed by MariaDB.

The last method of insertion that we are skipping for now is to insert the data that has been retrieved on the fly from a table. We will be looking at that once we have covered the methods for retrieving data and filtering data.