Copyright

SQL: INSERT Statement

Instructor: Martin Gibbs

Martin has 16 years experience in Human Resources Information Systems and has a PhD in Information Technology Management. He is an adjunct professor of computer science and computer programming.

The SQL INSERT statement, which is used to add new records in a database table, is one of the most basic commands in the language. In this lesson, we'll learn the syntax of this command and examine examples of the statement.

How Do We Add Information to the Table?

A database table is a great, but fairly useless if you can't insert data into it. In SQL, the statement used to insert new record(s) or data into tables is INSERT. It's considered part of the category of statements called Data Manipulation Language (DML), because it is used to manipulate data in the database. In this case, it is used to insert new records into tables.

In order for INSERT to work, you need to specify the fields receiving the data, and the values for those fields. The data type must match the field type; in other words you shouldn't try to enter a zip code into a name field!

The basic syntax for INSERT is:


INSERT INTO table (column_name1, column_name2, ...)
FROM other_table
[WHERE condition];


This means that you need to tell the database which table is going to be updated with new data, which fields/columns are being added, and what the values of those fields/columns are. Remember that we often refer to fields as columns and vice versa; they are the same thing. If it helps to keep things clear, remember that you row your boat across the columns, and down the rows of data.

It is also important to remember that the order you choose for the columns must match the values you specify. It is very easy to mix them up, and not cause errors- for string fields you could wind up with the employee address in the last name field!

Insert Multiple Records

Invariably, you'll want to insert multiple records into the table. A commands exists for this function. This can be done to create a backup of the data, or to create an audit record.


INSERT INTO table (column_name1, column_name2, ...)
SELECT expression
FROM other_table
[WHERE condition];


There are two more arguments added to this syntax: other_table and SELECT .... WHERE: other_table, and SELECT ... WHERE. If you are reading data from another table and inserting into another, the database needs to know which table, and what records. These added commands make sure that you get the right data from the original table and insert it into the new one.

Note the semicolon at the end of the line of every example so far - this is required in most database management systems! If you leave out, the statement may not run at all, or you will get unexpected (not good) results.

SQL INSERT Examples

Now that we have an understanding of the basic syntax, let's apply those examples.

Insert Single Record

In this example, we have a music database that has a table for albums. To insert a new album into the table, we would type in the following statements:


INSERT INTO tblAlbum(artistName, albumTitle, genre)


Our album table will now look like:

artistName albumTitle genre
Journey Raised on Radio Rock

Inserting Without All Columns

As we mentioned before, it isn't always necessary to insert values for all columns. Unless the table field/column requires a value, you are not required to insert a value.

In the following example, we will insert a new artist/album but leave the genre blank.


INSERT INTO tblAlbum(artistName, albumTitle)
VALUES('Meat Loaf', 'Bat out of Hell');


Now the table looks like:

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support