How to avoid ORA-00947 "not enough values" when performing an ITAS operation

If you are inserting rows from a table using an ITAS (Insert Tables As Select) statement, and the table being written to has a different layout than the table being selected from, you may struggle with getting your INSERT statement to succeed.

For example, let's say that the interim_table below has a virtual column defined on it, that the original table does not have. When attempting to execute your ITAS statement, you may see the error

ORA-00947: not enough values

To avoid this, enclose the the column listing in parenthesis, while omitting it for the SELECT statement constituting the VALUES-list, like this:

insert into interim_table
( 
  tq_id
  ,ename
  ,setting
)
  SELECT
    f.tq_id,
    f.ename,
    f.setting
  FROM original_table f JOIN interim_table wf
  ON wf.tq_id = f.tq_id;