The Column List

The column list determines the order in which values are entered.

For example, suppose that you have a table called newpublishers that is identical in structure and content to the publishers table in pubs2. In the example below, the columns in the column list of the newpublishers table match the columns of the select list in the publishers table.

insert newpublishers (pub_id, pub_name) 
select pub_id, pub_name 
    from publishers 
    where pub_name="New Age Data"

The pub_id and pub_name for “New Age Data” are stored in the pub_id and pub_name columns of newpublishers.

In the next example, the order of the columns in the column list of the newpublishers table does not match the order of the columns of the select list of the publishers table.
insert newpublishers (pub_id, pub_name) 
    select pub_name, pub_id 
    from publishers 
    where pub_name="New Age Data" 

The result is that the pub_id for “New Age Data” is stored in the pub_name column of the newpublishers table, and the pub_name for “New Age Data” is stored in the pub_id column of the newpublishers table.

You can omit items from the column and values lists as long as the omitted columns allow null values (see Example 2).