Lesson 15. Inserting Data

In this lesson, you will learn how to insert data into tables using the SQL INSERT statement.

Understanding Data Insertion

SELECT is undoubtedly the most frequently used SQL statement (which is why the last 14 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.

Tip: 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.

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:

Input ▾

INSERT INTO Customers
VALUES(1000000006,
       'Toy Land',
       '123 Any Street',
       'New York',
       'NY',
       '11111',
       'USA',
       NULL,
       NULL);

Analysis ▾

The above example inserts a new customer into the Customers 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 cust_contact and cust_email columns above), 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.

Tip: 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:

Input ▾

Click here to view code image

INSERT INTO Customers(cust_id,
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state,
                      cust_zip,
                      cust_country,
                      cust_contact,
                      cust_email)
VALUES(1000000006,
       'Toy Land',
       '123 Any Street',
       'New York',
       'NY',
       '11111',
       'USA',
       NULL,
       NULL);

Analysis ▾

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.

Note: 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 a customer with an ID of 1000000006 already existed. As discussed in Lesson 1, “Understanding SQL,” primary key values must be unique, and because cust_id is the primary key, the DBMS won’t allow you to insert two rows with the same cust_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 the next 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:

Input ▾

Click here to view code image

INSERT INTO Customers(cust_id,
                      cust_contact,
                      cust_email,
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state,
                      cust_zip)
VALUES(1000000006,
       NULL,
       NULL,
       'Toy Land',
       '123 Any Street',
       'New York',
       'NY',
       '11111');

Tip: 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.

Caution: 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.

Inserting Partial Rows

As I 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:

Input ▾

Click here to view code image

INSERT INTO Customers(cust_id,
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state,
                      cust_zip,
                      cust_country)
VALUES(1000000006,
       'Toy Land',
       '123 Any Street',
       'New York',
       'NY',
       '11111',
       'USA');

Analysis ▾

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

Caution: 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.

Caution: 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.

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 customers from another table into your Customers table. Instead of reading one row at a time and inserting it with INSERT, you can do the following:

Input ▾

Click here to view code image

INSERT INTO Customers(cust_id,
                      cust_contact,
                      cust_email,
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state,
                      cust_zip,
                      cust_country)
SELECT cust_id,
       cust_contact,
       cust_email,
       cust_name,
       cust_address,
       cust_city,
       cust_state,
       cust_zip,
       cust_country
FROM CustNew;

Note: Instructions Needed for the Next Example

The following example imports data from a table named CustNew into the Customers table. To try this example, create and populate the CustNew table first. The format of the CustNew table should be the same as the Customers table described in Appendix A, “Sample Table Scripts.” When populating CustNew, be sure not to use cust_id values that were already used in Customers. (The subsequent INSERT operation fails if primary key values are duplicated.)

Analysis ▾

This example uses INSERT SELECT to import all the data from CustNew into Customers. Instead of listing the VALUES to be inserted, the SELECT statement retrieves them from CustNew. 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 CustNew 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 Customers.

Tip: 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.

Tip: 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.

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 the CREATE SELECT statement (or SELECT INTO if using SQL Server).

Note: Not Supported by DB2

DB2 does not support the use of CREATE SELECT as described here.

Unlike INSERT SELECT, which appends data to an existing table, CREATE SELECT copies data into a new table (and, depending on the DBMS being used, can overwrite the table if it already exists).

The following example demonstrates the use of CREATE SELECT:

Input ▾

Click here to view code image

CREATE TABLE CustCopy AS SELECT * FROM Customers;

If using SQL Server, use this syntax instead:

Input ▾

Click here to view code image

SELECT * INTO CustCopy FROM Customers;

Analysis ▾

This SELECT statement creates a new table named CustCopy and copies the entire contents of the Customers table into it. Because SELECT * was used, every column in the Customers table will be created (and populated) in the CustCopy table. To copy only a subset of the available columns, you can specify explicit column names instead of the * wildcard character.

Here are some things to consider when using SELECT INTO:

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.

Tip: 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.

Note: More Examples

Looking for more examples of INSERT usage? See the example table population scripts described in Appendix A.

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.

Challenges

1. Using INSERT and columns specified, add yourself to the Customers table. Explicitly list the columns you are adding and only the ones you need.

2. Make backup copies of your Orders and OrderItems tables.