Skip to content

DML: Insert

Inserting a new row into a table is done using the SQL INSERT statement. There are two forms that you need to know about, and they are both covered below. For the purposes of the examples, we will be using the following pair of tables which might be found in a larger library system:

Books and copies

Inserting values directly

Often, you will already have a set of values that you want to use to create a new row in a table. In this case, you simply need to tell the database which value goes in which column. This is done by listing the columns first, and then by providing the values in the same order as shown in the statement below:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
    INSERT INTO book (isbn,
                      title,
                      main_author,
                      other_authors,
                      publisher,
                      publication_date)
              VALUES ('978-0321210258',
                      'Database Systems: A Practical Approach to Design,
                       Implementation and Management',
                      'Connolly, T.',
                      'Begg, C.',
                      'Addison Wesley',
                      '24-FEB-2009');

This book happens to have two authors, but that is not always the case. For a book with only one author, the other_authors column would be NULL, and there are two ways to do this. You can either list all the columns as before, and use the keyword NULL in place of the other_authors value, or you can just omit other_authors from the column list. The following statement is therefore also valid:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
    INSERT INTO book (isbn,
                      title,
                      main_author,
                      publisher,
                      publication_date)
              VALUES ('978-1111969592',
                      'Database Systems',
                      'Coronel, C.',
                      'South-Western College Publishing',
                      '31-JAN-2012');

Inserting rows using SELECT

Sometimes the values that you need for a new row are already stored in the database somewhere. For example, if you wanted to add a copy of Connolly and Begg to the library, you could find the ISBN number which is used as the primary key like this:

1
2
3
4
5
6
7
8
9
    INSERT INTO copy (copy_id,
                      isbn,
                      copy_status)
               SELECT 1200,
                      isbn,
                      'On shelf'
               FROM   book
               WHERE  title = 'Database Systems: A Practical Approach to
                               Design, Implementation and Management';

Notice that in the SELECT clause there is only one column value that comes from the BOOK table. The other two items - 1200 and 'On shelf' - are literals. This means that they are ordinary values that do not depend on a variable or column in a table.

Referencing sequences in INSERT statements

The example above uses a literal value for the copy_id, but of course in a library with thousands of books, it is unlikely you would happen to know what value to use. In practice you would want to use the next available value from a sequence. To do this explicitly, you would need to reference the NEXTVAL property of the sequence in the INSERT statement. The effect of this is to generate a new value ant to increment the sequence, and can be done as shown below assuming that there is a sequence called COPY_SEQ:

1
2
3
4
5
6
7
8
9
    INSERT INTO copy (copy_id,
                      isbn,
                      copy_status)
               SELECT copy_seq.nextval,
                      isbn,
                      'On shelf'
               FROM   book
               WHERE  title = 'Database Systems: A Practical Approach to
                               Design, Implementation and Management';

Occasionally, it can also be useful to find out what value was last generated by a sequence without incrementing it. This can be useful for example if you have just generated a new primary key in one table, and you then have to insert a set of records in a related table. For the related records, you would need to use the recently-generated value as a foreign key. To do this, you simply reference the CURRVAL property rather than NEXTVAL.

If you have created a trigger that automatically inserts a new value when a new record is created, you should leave the associated column out of the INSERT statement as if you were leaving it as NULL. The trigger will insert the key value before the operation completes. In this case, your INSERT statement would look like this:

1
2
3
4
5
6
7
    INSERT INTO copy (isbn,
                      copy_status)
               SELECT isbn,
                      'On shelf'
               FROM   book
               WHERE  title = 'Database Systems: A Practical Approach to
                               Design, Implementation and Management';

Finally, if you have specified a default value for a column in your table definition, you can also omit this column from the INSERT statement and the specified value will automatically be used. This could be done for example for the status column in the COPY table. By default, copies are available on the shelf when first recorded, and so the statement would become:

1
2
3
4
5
    INSERT INTO copy (isbn)
               SELECT isbn
               FROM   book
               WHERE  title = 'Database Systems: A Practical Approach to
                               Design, Implementation and Management';

Further reading

Insert

SQL INSERT INTO Statement