websql

19 Inserting Data

Learning Objectives

:

Describe the different ways the INSERT statement can be used.

Insert a complete row into a table, both with and without an explicit column list.

Insert a partial row into a table.

Use INSERT SELECT to insert the results of a query into a table.

Use CREATE TABLE AS SELECT (or SELECT INTO) to copy data into a new table.

19.1 Understanding Data Insertion

SELECT is undoubtedly the most frequently used SQL statement (which is why the last several lessons were dedicated to it). But there are three other frequently used SQL statements that you should learn. The first one is INSERT. (You'll get to the other two in the next lesson.)

As its name suggests, INSERT is used to insert (add) rows to a database table. INSERT can be used in several ways:

Inserting a single complete row
Inserting a single partial row
Inserting the results of a query

Let's now look at each of these.

INSERT and System Security. Use of the INSERT statement might require special security privileges in client/server DBMSs. Before you attempt to use INSERT, make sure you have adequate security privileges to do so.

19.2 Inserting Complete Rows

The simplest way to insert data into a table is to use the basic INSERT syntax, which requires that you specify the table name and the values to be inserted into the new row. Here is an example of this:

INSERT INTO artist
VALUES (501, 'Elena Voss', 'Elena', NULL, 'Voss', 'German', 'Abstract', 1962, NULL);

The above example inserts a new artist into the artist table. The data to be stored in each table column is specified in the VALUES clause, and a value must be provided for every column. If a column has no value (for example, the middle_names and death columns above, since this artist has no recorded middle name and is still living), the NULL value should be used (assuming the table allows no value to be specified for that column). The columns must be populated in the order in which they appear in the table definition — in this case, artist_id, full_name, first_name, middle_names, last_name, nationality, style, birth, death.

The INTO Keyword. In some SQL implementations, the INTO keyword following INSERT is optional. However, it is good practice to provide this keyword even if it is not needed. Doing so will ensure that your SQL code is portable between DBMSs.

Although this syntax is indeed simple, it is not at all safe and should generally be avoided at all costs. The above SQL statement is highly dependent on the order in which the columns are defined in the table. It also depends on information about that order being readily available. Even if it is available, there is no guarantee that the columns will be in the exact same order the next time the table is reconstructed. Therefore, writing SQL statements that depend on specific column ordering is very unsafe. If you do so, something will inevitably break at some point.

The safer (and unfortunately more cumbersome) way to write the INSERT statement is as follows:

INSERT INTO artist (artist_id, full_name, first_name, last_name, middle_names, style, nationality, birth, death)
VALUES (501, 'Elena Voss', 'Elena', 'Voss', NULL, 'Abstract', 'German', 1962, NULL);

This example does the exact same thing as the previous INSERT statement, but this time the column names are explicitly stated in parentheses after the table name. When the row is inserted, the DBMS will match each item in the columns list with the appropriate value in the VALUES list. The first entry in VALUES corresponds to the first specified column name. The second value corresponds to the second column name, and so on.

Because column names are provided, the VALUES must match the specified column names in the order in which they are specified, and not necessarily in the order that the columns appear in the actual table. The advantage of this is that, even if the table layout changes, the INSERT statement will still work correctly.

Can't INSERT Same Record Twice. If you tried both versions of this example, you'll have discovered that the second generated an error because an artist with an artist_id of 501 already existed. As discussed in the lesson on understanding SQL, primary key values must be unique, and because artist_id is the primary key, the DBMS won't allow you to insert two rows with the same artist_id value. The same is true for the next example. To try the other INSERT statements, you'd need to delete the first row added (as will be shown in a later lesson). Or don't, because the row has been inserted and you can continue the lessons without deleting it.

The following INSERT statement populates all the row columns (just as before), but it does so in a different order. Because the column names are specified, the insertion will work correctly:

INSERT INTO artist (artist_id, middle_names, death, first_name, last_name, style, nationality, birth, full_name)
VALUES (501, NULL, NULL, 'Elena', 'Voss', 'Abstract', 'German', 1962, 'Elena Voss');

Always Use a Columns List. As a rule, never use INSERT without explicitly specifying the column list. This will greatly increase the probability that your SQL will continue to function in the event that table changes occur.

Use VALUES Carefully. Regardless of the INSERT syntax being used, the correct number of VALUES must be specified. If no column names are provided, a value must be present for every table column. If column names are provided, a value must be present for each listed column. If none is present, an error message will be generated, and the row will not be inserted.

19.3 Inserting Partial Rows

As just explained, the recommended way to use INSERT is to explicitly specify table column names. Using this syntax, you can also omit columns. This means you provide values for only some columns, but not for others.

Look at the following example:

INSERT INTO artist (artist_id, first_name, last_name, style, nationality, birth)
VALUES (501, 'Elena', 'Voss', 'Abstract', 'German', 1962);

In the examples given earlier in this lesson, values were not provided for three of the columns, full_name, middle_names, and death. This means there is no reason to include those columns in the INSERT statement. This INSERT statement, therefore, omits those three columns and their three corresponding values.

Omitting Columns. You may omit columns from an INSERT operation if the table definition so allows. One of the following conditions must exist:

The column is defined as allowing NULL values (no value at all).

A default value is specified in the table definition. This means the default value will be used if no value is specified.

Omitting Required Values. If you omit a value from a table that does not allow NULL values and does not have a default, the DBMS will generate an error message, and the row will not be inserted.

19.4 Inserting Retrieved Data

INSERT is usually used to add a row to a table using specified values. There is another form of INSERT that can be used to insert the result of a SELECT statement into a table. This is known as INSERT SELECT, and, as its name suggests, it is made up of an INSERT statement and a SELECT statement.

Suppose you want to merge a list of newly catalogued artists from a staging table into your artist table. Instead of reading one row at a time and inserting it with INSERT, you can do the following:

INSERT INTO artist (artist_id, full_name, first_name, last_name, middle_names, style, nationality, birth, death)
SELECT artist_id, full_name, first_name, last_name, middle_names, style, nationality, birth, death
FROM new_artists;

Instructions Needed for the Next Example. The following example imports data from a table named new_artists into the artist table. To try this example, create and populate the new_artists table first. The format of the new_artists table should match the artist table described earlier in this book. When populating new_artists, be sure not to use artist_id values that were already used in artist. (The subsequent INSERT operation fails if primary key values are duplicated.)

This example uses INSERT SELECT to import all the data from new_artists into artist. Instead of listing the VALUES to be inserted, the SELECT statement retrieves them from new_artists. Each column in the SELECT corresponds to a column in the specified columns list. How many rows will this statement insert? That depends on how many rows are in the new_artists table. If the table is empty, no rows will be inserted (and no error will be generated because the operation is still valid). If the table does, in fact, contain data, all that data will be inserted into artist.

Column Names in INSERT SELECT. This example uses the same column names in both the INSERT and SELECT statements for simplicity's sake. But there is no requirement that the column names match. In fact, the DBMS does not even pay attention to the column names returned by the SELECT. Rather, the column position is used, so the first column in the SELECT statement (regardless of its name) will be used to populate the first specified table column, and so on.

The SELECT statement used in an INSERT SELECT can include a WHERE clause to filter the data to be inserted.

Inserting Multiple Rows. INSERT usually inserts only a single row. To insert multiple rows, you must execute multiple INSERT statements. The exception to this rule is INSERT SELECT, which can be used to insert multiple rows with a single statement; whatever the SELECT statement returns will be inserted by the INSERT.

19.5 Copying from One Table to Another

There is another form of data insertion that does not use the INSERT statement at all. To copy the contents of a table into a brand new table (one that is created on the fly), you can use CREATE TABLE AS SELECT.

Unlike INSERT SELECT, which appends data to an existing table, CREATE TABLE AS SELECT copies data into a new table (creating that table in the process — it will fail if a table by that name already exists).

The following example demonstrates the use of CREATE TABLE AS SELECT:

CREATE TABLE artist_copy AS SELECT * FROM artist;

PostgreSQL also supports an alternate, older syntax that accomplishes the same thing:

SELECT * INTO artist_copy FROM artist;

Either statement creates a new table named artist_copy and copies the entire contents of the artist table into it. Because SELECT * was used, every column in the artist table will be created (and populated) in the artist_copy table. To copy only a subset of the available columns, you can specify explicit column names instead of the * wildcard character. Between the two, CREATE TABLE AS SELECT is generally preferred, since its intent (creating a table) is obvious from the first keyword — SELECT INTO reads like an ordinary query until you notice the INTO clause. SELECT INTO also means something different when used inside a PL/pgSQL function or procedure (there, it assigns a query's result into a variable, as you saw in the lesson on cursors), which is one more reason CREATE TABLE AS SELECT is the safer habit to default to.

Here are some things to consider when using either form:

Any SELECT options and clauses may be used, including WHERE and GROUP BY.
Joins may be used to insert data from multiple tables.
Data may only be inserted into a single table regardless of how many tables the data was retrieved from.

Making Copies of Tables. The technique described here is a great way to make copies of tables before experimenting with new SQL statements. By making a copy first, you'll be able to test your SQL on that copy instead of on live data.

More Examples. Looking for more examples of INSERT usage? Experiment with the artist, work, museum, subject, and museum_hours tables introduced throughout this book.

19.6 Summary

In this lesson, you learned how to insert rows into a database table using INSERT. You learned several ways to use INSERT and why explicit column specification is preferred. You also learned how to use INSERT SELECT to import rows from another table and how to use SELECT INTO to export rows to a new table. In the next lesson, you'll learn how to use UPDATE and DELETE to further manipulate table data.