ORA-00947 - not enough values: Occurs in one server but not another

Question

I am work on a project which has to add one column to the exist table. It is like this:

The OLD TBL Layout

OldTbl(
  column1 number(1) not null,
  column2 number(1) not null 
);

SQL TO Create the New TBL

create table NewTbl(
  column1 number(1) not null,
  column2 number(1) not null,
  **column3 number(1)**
);

---

When I try to insert the data by the SQL below, on one oracle server,it was successful executed, but on another oracle server, I got "ORA-00947 error: not enough values"

insert into NewTbl select
column1,
column2
from OldTbl;

Is there any oracle option may cause this kind of difference in oracle?

Answer 1

ORA-00947: not enough values

this is the error you received, which means, your table actually has more number of columns than you specified in the INSERT.

Perhaps, you didn't add the column in either of the servers.

There is also a different syntax for INSERT, which is more readable. Here, you mention the column names as well. So, when such a SQL is issued, unless a NOT NULL column is missed out, the INSERT still work, having null updated in missed columns.

INSERT INTO TABLE1
(COLUMN1,
 COLUMN2)
SELECT
COLUMN1,
 COLUMN2
 FROM
 TABLE2