SQL INSERT Statement

SQL Tutorials 14 min read
SQL insert statement
Blog / SQL Tutorials / SQL INSERT Statement

SQL insert statement

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.

10 rows returned, sql insert statement

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.

 Syntax, sql insert statement

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.

Values, sql insert statement

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.

columns tab, sql insert statement sql insert statement

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.

employee number, sql insert statement

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.

John smith hire on 1 1 11, sql insert statement

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.

1 row affected, sql insert statement

Now, let’s select the 10 employees with the largest employee numbers, to see if our record will appear there.

limit 10, sql insert statement

Awesome! Here he is! John Smith is at the top!

sql insert statement

Let me comment on this bizarre number, 9-9-9-9-0-1.

999901, sql insert statement

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.

oder by emp no desc

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.)

string integer

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.

follow column order

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.

insert clause

Let’s see if it works…

inserting data values in different order

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.

insert into employees

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.

trying to create a record in the “Employees” table with only four instead of all six columns insert into employees error code 1136

If we try to add six data values to the new record, all of them in the correct order, the query should work fine.

6 values in order

Let’s check.

adding six data values to the new record in the correct order

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.

using 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.

dept no and dept name

So, the next thing to do is create another table, called “departments duplicate”, shortening it to “departments underscore D.U.P.”

departments dup

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.

import all the data from departments into departments_dup

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.

create table department dups

Execute … and then refresh the schemas section in Workbench.

refreshing the schemas in workbench departments_dup table has been added to the list

Here we are!

The new table has been added to the list. Furthermore, the “department number” and “department name” columns are visible, too.

department number and department name columns in the departments_dup table

To verify that this is still an empty table, we’ll have to select all the information from it.

verify the table is empty

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.

INSERT INTO the “department number” and “department name” columns from the “Departments duplicate” table everything that can be selected 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.)

Star (*) Wildcard

Right! So, let’s run this query.

Ok! The code was executed with no errors.

Let’s see if the operation was performed correctly.

"departments duplicate” table filled with information from the “departments table”

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.

DDL for employees departments with primary key and unique key ticked

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? Learn how to put theory into practice with our hands-on tutorials!

Check out our Next Tutorial: SQL UPDATE Statement

 

Earn your Data Science Degree

Expert instructions, unmatched support and a verified certificate upon completion!

Leave a Reply

Your email address will not be published.

You have Successfully Subscribed!