Now that you have installed MySQL we will explore the SQL INSERT statement in a bit more detail.
Create a Record
Let’s practice inserting a few records in an “Employees” table. (If you're interested, there's more on records in our tutorial What are Duplicate Records in SQL).
We can extract 10 records from the “Employees” table to see how the information is organized there. Great! So…
We retrieved 10 rows, all containing information in the following columns: “employee number”, “birth date”, “first” and “last name”, “gender”, and “hire date”. A total of six columns. Ok.
Let’s see how we create a record in the “Employees” table.
After the keyword phrase INSERT INTO, we must specify the name of the table where we want to add information. Then, within parentheses, we must indicate the column names where data will be inserted.
Please pay attention – you don’t have to use the names of all six columns! You can designate only those in which you would like to insert data.
What follows is intuitive – the VALUES keyword, succeeded by the same number of data values as the number of columns indicated in the parentheses after the INSERT INTO statement. Note that the data values should be typed within parentheses, too.
You are already acquainted with the syntax we just discussed. Now let’s put it into practice – we’ll create a record of an individual named John Smith.
We’ll type INSERT INTO “Employees” and then, within parentheses, specify the columns we want to add information into, separating them by a comma. These columns are “employee number”, “birth date”, “first name”, “last name”, “gender”, and “hire date”. Then, after the keyword VALUES, and within parentheses, type the data values that will be part of these columns, again separating them by a comma.
To see the data types of the values you will insert, you must check the data types of this table’s fields. You can do this by going to the information section and looking at the data types in the “Columns” tab.
The employee number is an integer; then you have two date values, two VARCHAR values, and an ENUM. Therefore, as you insert the new records, all values must be written in single quotes, besides the employee number.
Let’s assign the number 9-9-9-9-0-1, and a birth date of the 21st of April 1986. This record will refer to John Smith, a male, who was hired on the 1st of January 2011.
To register this row in the data table, we’ll need to execute the INSERT query.
Side note: If you want to learn how to incorporate SQL subqueries within queries, check out the linked tutorial.
All right, we see that we have one row affected.
Now, let’s select the 10 employees with the largest employee numbers, to see if our record will appear there.
Awesome! Here he is! John Smith is at the top!
Let me comment on this bizarre number, 9-9-9-9-0-1.
Obviously, this is not the next consecutive number in our super-long list of employees, is it? Remember that using such an employee number is a neat professional trick. When data is being created to test the database system, the value inserted should be quite high. This way, it is obvious where the new data has been placed. (In case you need a refresher, follow the link to our tutorial First Steps into Data Manipulation and Operators in SQL.)
For this reason, we saw John Smith on the top of the 'employees' list, with the highest employee number.
Now, let me share another MySQL feature with you. Contrary to anything we’ve said so far, it turns out that integers can be written within quotes, too. If you wish, you can try inserting another record with the employee number within quotes; you’ll see the data still registered as an integer. The explanation why is that MySQL will automatically (or, as some professionals would say, transparently) convert the string into an integer. The best practice is to avoid writing integers within quotes since this conversion consumes time and prevents the smooth operation of more advanced MySQL features. (In the meantime, if you want to learn how to make MySQL run faster, check out our tutorial Working with indexes in MySQL.)
To conclude, the idea is that, yes, you could write an integer within quotes, but this is not considered a best practice. That’s why we would like to give you the following piece of advice. Please remember to type integers as plain numbers, without using quotes.
Great! Let’s keep up the pace for the next section.
INSERT clause
Ok. Perfect. Let’s go ahead with the INSERT clause.
Usually, database administrators follow the order of columns in terms of the way they appear in a given data table.
However, on certain occasions, they prefer inserting data values in a different order. MySQL allows for such types of data insertion. For instance, should we create a data record about Patricia Lawrence, we could insert her birth date first, then her employee number, and then cite the rest of the values in the designated order.
Let’s see if it works…
One hundred percent! The last row we created contains information about Patricia Lawrence!
So, please keep in mind that we must put the VALUES in the exact order we have listed the column names.
Fantastic!
Ok. Let’s take a look at an interesting feature of the INSERT syntax. Technically, the first pair of parentheses, along with the column names between them, can be omitted. Only INSERT INTO, the table name, and the VALUES parts are mandatory.
If you omit it, in the VALUES section you will have to specify as many data values as there are columns in the data table. Furthermore, you will have to add them in the same order in which they appear in the table.
For example, if we use this structure and try to create a record in the “Employees” table with only four instead of all six columns, MySQL will display an error.
If we try to add six data values to the new record, all of them in the correct order, the query should work fine.
Let’s check.
Good. The new piece of information is there.
In the next section, we will show you a very powerful feature of the INSERT statement.
Inserting Data INTO a New Table
In this section, we will show you another way to insert data into a table.
The relevant syntax is:
INSERT INTO, table name, and column names in parentheses. Then comes the new part. You can use a classical SELECT statement to retrieve information from table_1 and insert it into table_2.
It doesn’t have to be the entire data from that other table – by using WHERE you should be able to set conditions that would refine the data to be copied.
Let’s see an example.
First, check the columns of the “departments” table. We have two columns – department code and department name.
So, the next thing to do is create another table, called “departments duplicate”, shortening it to “departments underscore D.U.P.”
It will be a replica of the “departments” table we just saw. Our task is to import all the data from “departments” into its duplicate.
The code we’ll need to create the copy table is CREATE TABLE “Departments Duplicate”, open parentheses, “department number”, CHAR type of 4, NOT NULL constraint added, then “department name”, VARCHAR of 40, NOT NULL constraint again, close parentheses.
Execute … and then refresh the schemas section in Workbench.
Here we are!
The new table has been added to the list. Furthermore, the “department number” and “department name” columns are visible, too.
To verify that this is still an empty table, we’ll have to select all the information from it.
You see? There is no information.
Now is the time to implement the structure we introduced in this post. We will INSERT INTO the “department number” and “department name” columns from the “Departments duplicate” table everything we can select from the “Departments” table.
Please note that since “Departments” contains the same number and type of columns, it is not necessary to add specific conditions to the data retrieved from that table. Hence, the star (*) wildcard character will do the job properly. (You can study the main types of relationships between tables in SQL in the linked tutorial.)
Right! So, let’s run this query.
Ok! The code was executed with no errors.
Let’s see if the operation was performed correctly.
It was. Most definitely! We see that the newly created “departments duplicate” table has been filled with information from the “departments table”. That’s awesome!
In other words, our job is done!
I’d like to wrap up this post with a reminder about MySQL constraints. We barely mentioned them in this section. However, you know that unless the new data you are inserting satisfies the constraints that have already been set in the database, MySQL will always show an error.
For simplicity, we carefully designed our code, and such errors were avoided. In other words, we ensured we did not insert data that does not satisfy the existing constraints. Nevertheless, keep in mind that complying with constraints is essential – they will always play a major role when inserting data.
***
Eager to hone your SQL skills? Take our SQL course.
Check out our Next Tutorial: SQL UPDATE Statement
