Example: Insert Using a SELECT Subquery Without Target Column List
The INSERT operation performed in the previous example can also be accomplished by the following statement. Note that a column name list is not given for the promotion table; therefore, the employee columns referenced in the SELECT subquery must match, in both quantity and sequence, the columns contained by promotion.
INSERT INTO promotion
SELECT name, deptno, yrsexp
FROM employee
WHERE yrsexp > 10 ;