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

Answer 2

insert into NewTbl select
column1,
column2
from OldTbl;

The above query is wrong, because your new table has three columns, however, your select has only two columns listed. Had the number and the order of the columns been same, then you could have achieved it.

If the number of the columns, and the order of the columns are different, then you must list down the column names in the correct order explicitly.

I would prefer CTAS(create table as select) here, it would be faster than the insert.

CREATE TABLE new_tbl AS
SELECT column1, column2, 1 FROM old_tbl;

You could use NOLOGGING and PARALLEL to increase the performance.

CREATE TABLE new_tbl NOLOGGING PARALLEL 4 AS
SELECT column1, column2, 1 FROM old_tbl;

This will create the new table will 3 columns, the first two columns will have data from the old table, and the third column will have value as 1 for all rows. You could keep any value for the third column as per your choice. I kept it as 1 because you wanted the third column as data type NUMBER(1).