Insert

It is possible to write the INSERT INTO statement in two ways.

• Inserting Partial Rows. We need to specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

• Inserting Complete Rows. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table.

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Inserting Partial Rows

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.

To insert partial rows, just specify the columns names that have a value and then specify the corresponding values in VALUES clause.

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

Inserting Complete Rows

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

This is not safe because the order might be different. The following is recommended:

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

As a rule, never use INSERT without explicitly specifying the column list.

Inserting Retrieved Data

To insert retrieved data from another table, use INSERT INTO SELECTwhich is made up of an INSERT INTO statement and a SELECT statement.

Suppose you want to merge a list of customers from another table into your Customers table. 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;