Inserting Data without Specifying Column Names

INSERT statements can also be executed without the specification of column names. To execute an INSERT statement without typing the column names, specify the values in the same order that the columns appear in the table. Look at Example-F, which inserts an additional record into the Toys table.

Example-F

Say you want to insert a complete record into the Toys table but you do not want to type the column names. Look at the following script:

INSERT INTO Toys
VALUES (6, 'DollHouse', 17.00, 'Grand Town House');

The preceding script inserts one record containing four values into the Toys table. Because the values are typed in the same order in which the columns appear in the table, it is not necessary to type the column names.

As a side note, if you want to insert values into specific columns only, specify only the column names you want to insert values into. Next, specify values in the same order as they appear in your INSERT statement.

Inserting NULL Values

Example-G

Say you want to insert a record with a missing value. Take a look at the following script:

INSERT INTO Toys
VALUES (7, 'Doll/TownHouse', 15.00, NULL);

This script inserts one record containing three values into the Toys table. It inserts NULL for a missing value. Recall that NULL means no value.

Copying Records from One Table to an Existing Table

Example-H

Sometimes it is necessary to populate a table with records from an existing table. Say, for example, you need to create a test table and you want to use data that is already stored in another table. Take a look at the following scripts. The first one creates a new table named ToysTest and the second one copies the records from the Toys table to the ToysTest table.

    CREATE TABLE ToysTest
    (
    ToyID CHAR (7) CONSTRAINT ToyPk PRIMARY KEY,
    ToyName CHAR (30) NOT NULL,
    Price MONEY NOT NULL,
    Description CHAR (40) NULL
    );

This script creates a table named ToysTest. The ToysTest table contains the same data types and field sizes as the Toys table. The following script copies the records from the Toys table into the ToysTest table:

INSERT INTO ToysTest (ToyID, ToyName, Price, Description)
SELECT ToyID, ToyName, Price, Description
FROM Toys;

This script uses the INSERT INTO keywords to specify the table name and column names to insert records into. The SELECT and FROM keywords are used to specify the column names and table name from which to retrieve the records to insert. The SELECT keyword is used to specify the column names from the Toys table and the FROMkeyword is used to specify the Toys table.