INSERT INTO to Add Row

1. Using the INSERT INTO Statement

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. Next, we have a list of the values in parenthesis after the VALUES keyword in the same order that we have the columns. 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 if the column has a sequence attached to it, which we will address in the next tutorial.

2. Examples

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

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

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

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

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

However, this is unreliable error-checking. It catches that we tried to insert a string into an integer column. There are many other instances where we may be inserting wrong data into the same data type, like swapping two integer value columns. 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, such as foreign keys, as this can prevent data from being inserted. 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:

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');

However, if we accidentally swapped the values:

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

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

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.