Inserting Data

Understanding Data Insertion

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

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 Customers
VALUES('1000000006',
       'Toy Land',
       '123 Any Street',
       'New York',
       'NY',
       '11111',
       'USA',
       NULL,
       NULL); 

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.

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

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

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.

The advantage of this is that, even if the table layout changes, the INSERT statement will still work correctly.

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

Inserting Partial Rows

The recommended way to use INSERT is to explicitly specify table column names. Using this syntax, you can also omit columns. This means you only provide values for some columns, but not for others. Look at the following example:

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

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.

Inserting Retrieved Data

You can use the INSERT SELECT statement to insert data retrieved from another table.

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:

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;

Copying from One Table to Another

SELECT INTO copies data into a new table.

SELECT *
INTO CustCopy
FROM Customers;

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, explicit column names can be specified 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.