Copy rows from one table to another

Rows can be inserted into a table by copying data from another table and identifying columns to be inserted with a subquery instead of using the VALUES clause with INSERT. The information retrieved by the subquery is placed into the table as if multiple INSERT commands had been entered.

The following statement copies the ID, NAME, JOB, and YEARS columns for members of Department 38 from Q.STAFF into PERS:

 INSERT INTO PERS (ID, NAME, JOB, YEARS)
 SELECT ID, NAME, JOB, YEARS
 FROM Q.STAFF
 WHERE DEPT = 38

Values must be specified for all columns that are defined as NOT NULL.

A one-to-one correspondence does not have to exist between columns being selected and columns being inserted; however, there should not be more columns selected than columns being inserted. If fewer columns are selected than are being inserted, the remaining columns are inserted with nulls. Rows cannot be selected for insertion into the same table.

For information about authorization, see GRANT.