Online College Courses for Credit

+
INSERT INTO to Add Row

INSERT INTO to Add Row

Rating:
(0)
Author: Sophia Tutorial
Description:

Compose an INSERT INTO statement that adds a single record to an existing table, including all necessary (non NULL) fields.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

what's covered
This tutorial explores using INSERT statements to add a single row into an existing table.

We have used the INSERT statement a few times in prior tutorials, but we will get a chance to explore the details around the statement in this tutorial. The basic syntax of the INSERT statement looks like this:

INSERT INTO <tablename> ( <column1>, <column2>, …)
VALUES (<value1>, <value2>, …);

To break this down, we have to identify the table that we want to insert into to replace <tablename> after the keywords INSERT INTO. Then we must list the column names separated by commas. Then, we have a list of the values in parenthesis after the VALUES keyword in the same order that we have the columns. Note that it is important that we use quotes around dates, characters, and strings in the value list. Note that we must include all columns in a table that has a PRIMARY KEY or NOT NULL constraint in place. The only exceptions are if the column has a DEFAULT value or the column is has a sequence attached to it which we will get to in the next tutorial.

For example, to insert into the artist table, we can run the following statement:

INSERT INTO artist (artist_id, name)
VALUES (1000,'Bob Dylan');

File:11344-1400-1.png

We can change the order of the columns if we also change the order of the values to match:

INSERT INTO artist (name, artist_id)
VALUES ('Michael Jackson', 1001);

File:11345-1400-2.png

If we accidentally swapped the values and columns, we may get an error:

INSERT INTO artist (name, artist_id)
VALUES (1001, 'Michael Jackson');

File:11346-1400-3.png

However, this is unreliable as it is finding that we tried to insert a string into an integer column. There are many other instances where we may be inserted into the same data type. It is important that we are double-checking the order of the column list and the value list to match.

We do have to be careful of the existing constraints on a table as this can prevent data from being inserted such as foreign keys. For example, if we tried to insert the following statement:

INSERT INTO album (artist_id, album_id, title)
VALUES (999, 2000,'Latest Hits');

The artist_id in the album table references the artist table. If we looked at the artist table for the artist_id equal to 999, we will find that this doesn’t exist:

SELECT *
FROM artist
WHERE artist_id = 999;

As such, trying to insert it into the table would result in an error:

File:11347-1400-4.png

If we wanted to add the album Latest Hits to the album table using the artist_id 1000 that we inserted for Bob Dylan, the INSERT statement would look like this:

INSERT INTO album (artist_id, album_id, title)
VALUES (1000, 1001,'Latest Hits');


File:11348-1400-5.png

However, if we accidentally swapped the values:

INSERT INTO album (artist_id, album_id, title)
VALUES (1001, 1000,'Latest Hits');

File:11349-1400-6.png

No error would be displayed in this case as we also have added an artist with the artist_id with the value of 1001. This is why it is important to be careful of what the values represent as these logical errors would not be caught.


try it
Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.

summary
The INSERT INTO statement allows us to insert data into an existing table.