The Column List

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.

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