Populating a MySQL table with the INSERT statement – Beginner Series.

This third installment of the MySQL Beginner Series Basics will highlight INSERT syntax for populating tables with data. Be sure and visit both blog posts: CREATE Database, User, and Table – Easy examples with MySQL along with, ALTER TABLE: Examples with MySQL – Beginner Series, for an overview of the material covered so far in the series.

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

I will be using Xubuntu Linux 16.04.2 LTS (Xenial Xerus) and MySQL 5.7.20 for these exercises.

Objectives.

  • Perform a single INSERT using mysql interactively from the command line.
  • Perform multiple INSERTS using mysql interactively from the command line.
  • Use mysql in batch mode to INSERT multiple rows via a .sql source file.

Insert Syntax

INSERT populates an existing table with new rows of data.
Basic INSERT syntax can be as follows:

INSERT INTO table_name (optional comma-separated list of column names)
VALUES (list of values to insert) or
[multiple lists of values
(list of values to insert),
(list of values to insert),
(list of values to insert),]
etc...;

Single Inserts

To INSERT a single record interactively from the command line, the following syntax can be used:

mysql> INSERT INTO friends(f_first_name, f_last_name, f_cell_phone, f_age)
    -> VALUES('John','Tom','318-767-8818',32);
Query OK, 1 row affected (0.08 sec)

This INSERT statement lists out the column names in a comma-separated, parenthesized list containing the columns to populate. Followed by the VALUES keyword and another comma-separated list of the actual values to be inserted into the listed columns.
Notice for this INSERT, the f_bday column was not specified, nor any value for it either.
Yet when I query the friends table for that row of data,

mysql> SELECT * FROM friends
    -> WHERE f_last_name = 'Tom';
+-----------+--------------+-------------+--------------+-------+--------+
| friend_id | f_first_name | f_last_name | f_cell_phone | f_age | f_bday |
+-----------+--------------+-------------+--------------+-------+--------+
|         1 | John         | Tom         | 318-767-8818 |    32 | NULL   |
+-----------+--------------+-------------+--------------+-------+--------+
1 row in set (0.04 sec)

NULL is present for the f_bday column.
So what gives?
Since f_bday was not specified in the column list or list of values, MySQL automatically populated it with a DEFAULT value,  NULL, which was specified during column creation.
More to come on excluding column names and or values as we progress through this blog post.

Multiple Inserts

Multiple rows can be inserted interactively from the command line using similar syntax as follows:

mysql> INSERT INTO friends(f_first_name, f_last_name, f_cell_phone, f_age, f_bday)
    -> VALUES
    -> ('John','Roberts','555-867-5309', 36, '1981-04-17'),
    -> ('Billy','Bogus','777-383-2828', 44, '1973-08-03'),
    -> ('Jennifer','Kelly','979-191-6652',39, '1978-10-16');
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

Notice after the VALUES syntax, multiple parenthesized comma-separated list, each containing column values, are themselves separated by commas.

Information String:
Records: number Duplicates: number Warning: number
The above INSERT returns an information string, which I directly relay its interpretation from the official documentation (see link in Closing section):
“Records indicates the number of rows processed by the statement. (This is not necessarily the number of rows actually inserted because Duplicates can be nonzero.) Duplicates indicate the number of rows that could not be inserted because they would duplicate some existing unique index value. Warnings indicate the number of attempts to insert column values that were problematic in some way. Warnings can occur under any of the following conditions.”
(See the official documentation for the Warning conditions.)

Default values.

Let’s try a single INSERT without a parenthesized list of column names:

mysql> INSERT INTO friends
    -> VALUES ('Mark', 'Harmon', '567-733-0833', 28, '1989-06-27');
ERROR 1136 (21S01): Column count doesn't match value count at row 1

Uh oh. That didn’t work.
By not providing a list of column names, values for every column must be present in the VALUES syntax list.
To accomplish this, I use the DEFAULT keyword for the friend_id column value. Since this column has the AUTO_INCREMENT attribute, it will automatically INSERT the next available sequential value.

mysql> INSERT INTO friends VALUES (DEFAULT, 'Mark', 'Harmon', '567-733-0833', 28, '1989-06-27');
Query OK, 1 row affected (0.02 sec)

Querying for the f_first_name value of Mark, returns a full row of data with a friend_id column value of 5 .

mysql> SELECT * FROM friends WHERE f_first_name = 'Mark';
+-----------+--------------+-------------+--------------+-------+------------+
| friend_id | f_first_name | f_last_name | f_cell_phone | f_age | f_bday     |
+-----------+--------------+-------------+--------------+-------+------------+
|         5 | Mark         | Harmon      | 567-733-0833 |    28 | 1989-06-27 |
+-----------+--------------+-------------+--------------+-------+------------+
1 row in set (0.00 sec)

To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.

Batch Mode For Multiple Inserts

Finally, I will cover my personal favorite method for bulk inserts. Using mysql in batch mode.
I have a batch_insert.sql file containing these inserts:

INSERT INTO friends(f_first_name, f_last_name, f_cell_phone, f_age, f_bday)
VALUES
('Jeremy', 'Hall', '777-272-1717',40 , '1977-02-07'),
('Josh', 'Remey', '727-213-7119',40 , '1977-03-17'),
('Jenny', 'Marcum', '543-179-1548',37 , '1980-09-27'),
('Mike', 'Comb', '118-972-1919',30 , '1987-10-05'),
('Thurmon', 'Docker', '393-392-1010',25 , '1992-08-19'),
('Milton', 'Hammerfast', '318-272-0797',41 , '1976-05-11'),
('Nikki', 'Vernon', '548-498-9949',28 , '1989-01-31'),
('Avis', 'Pearl', '509-292-8787',64 , '1953-07-04');

To execute this .sql file, simply prefix either a \. or the source word before the file name as demonstrated below.

Note: For demonstration purposes, the file path presented below is generic. My actual system file path for this script file is not shown.

mysql> source /my_dir/my_system_user/My_Folder/sub_folder/batch_insert.sql
Query OK, 8 rows affected (0.06 sec)
Records: 8 Duplicates: 0 Warnings: 0

For visual confirmation, I issue a simple SELECT query for just the f_first_name column (Note: There are additional names already present in the table so the result set will have more than 8 rows.),

mysql> SELECT f_first_name FROM friends;
+--------------+
| f_first_name |
+--------------+
| John         |
| John         |
| Billy        |
| Jennifer     |
| Mark         |
| Jeremy       |
| Josh         |
| Jenny        |
| Mike         |
| Thurmon      |
| Milton       |
| Nikki        |
| Avis         |
| Josh         |
+--------------+
14 rows in set (0.00 sec)

Using batch mode, in my opinion, is an effective way for large, bulk inserts.

An overview of the entire friends table, containing all the inserts performed in this blog post (among others already present in the table),
mysql> SELECT * FROM friends;
+-----------+--------------+-------------+--------------+-------+------------+
| friend_id | f_first_name | f_last_name | f_cell_phone | f_age | f_bday     |
+-----------+--------------+-------------+--------------+-------+------------+
|         1 | John         | Tom         | 318-767-8818 |    32 | NULL       |
|         2 | John         | Roberts     | 555-867-5309 |    36 | 1981-04-17 |
|         3 | Billy        | Bogus       | 777-383-2828 |    44 | 1973-08-03 |
|         4 | Jennifer     | Kelly       | 979-191-6652 |    39 | 1978-10-16 |
|         5 | Mark         | Harmon      | 567-733-0833 |    28 | 1989-06-27 |
|         6 | Jeremy       | Hall        | 777-272-1717 |    40 | 1977-02-07 |
|         7 | Josh         | Remey       | 727-213-7119 |    40 | 1977-03-17 |
|         8 | Jenny        | Marcum      | 543-179-1548 |    37 | 1980-09-27 |
|         9 | Mike         | Comb        | 118-972-1919 |    30 | 1987-10-05 |
|        10 | Thurmon      | Docker      | 393-392-1010 |    25 | 1992-08-19 |
|        11 | Milton       | Hammerfast  | 318-272-0797 |    41 | 1976-05-11 |
|        12 | Nikki        | Vernon      | 548-498-9949 |    28 | 1989-01-31 |
|        13 | Avis         | Pearl       | 509-292-8787 |    64 | 1953-07-04 |
|        14 | Josh         | Otwell      | 318-282-2075 |    41 | NULL       |
+-----------+--------------+-------------+--------------+-------+------------+
14 rows in set (0.00 sec)

Insert Takeaways For This Post…

      • A user needs the INSERT privilege for the specified table to be populated.
      • Column names may be supplied in any of the following ways:
          • A parenthesized list of comma-separated column names. In this instance, a value for each specified column must be in the VALUES list. (Also applies for inserting with the SELECT statement, however that functionality is not covered in this blog post.)
          • If not specifying a list of column names, values must be supplied for every column in the table, in order. However the DEFAULT keyword can be used for those columns that have a DEFAULT value set at table creation.
      • Column values can supplied as follows:
        • When strict SQL mode is not enabled, column values not explicitly provided, are set to the DEFAULT value for that column, with either the table-defined or data type DEFAULT.
        • INSERT produces an error if no values are specified for all columns that don’t have a default value when strict SQL mode is enabled.
        • INSERT INTO table_name () VALUES (); – Is allowable, yet MySQL handles this as follows:
            • When strict mode is not enabled, an implicit DEFAULT value is used for a column that has no explicitly defined DEFAULT.
            • An error occurs if any columns have no default value when strict mode is enabled.
            • The DEFAULT keyword can be used to explicitly set a column to its default value.

Closing.

Inserts are a powerful feature of MySQL and are a necessity for database operations. After all, the tables need data, right?
Stay tuned to this continuing series as the next blog post will look into two of the more powerful MySQL commands: UPDATE and DELETE.

Feel free to visit the official MySQL 5.7 Online Manual for further study or questions.

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well. Have I mentioned how much I love a cup of coffee?!?!

To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar!
Be sure and visit the “Best Of” page for a collection of my best blog posts.

Josh Otwell

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

2 thoughts on “Populating a MySQL table with the INSERT statement – Beginner Series.”

  1. Pingback: SELECT clause queries – MySQL Beginner Basics Series. | Digital Owl's Prose
  2. Pingback: ALTER TABLE: Examples with MySQL – Beginner Series. | Digital Owl's Prose

Hey thanks for commenting! Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.