ORA-00947: not enough values error message in Oracle DB

Concept:

This error occurs when you are trying to insert data from table with less columns into table with more columns. Your sub query output contains less columns and the table where data is to be inserted is having more columns.

For Example:

• Create a table A having number of columns are 3.
• Create a table B having number of columns are 2.
• Insert any data into table.
• Try to insert data from table B into table A, you  will receive an below error message.

            “ORA-00947: not enough values”

SQL> create table a (id number, name varchar2(12),address varchar2(12)); Table created. SQL> select * from a; no rows selected SQL> create table b (id number,name varchar2(12)); Table created.                               SQL> insert into b values(10,'Harry'); 1 row created. SQL> insert into b values(11,'Potter'); 1 row created. SQL> insert into b values(12,'Ganesha'); 1 row created. SQL> commit; Commit complete. SQL> select * from b;             ID NAME ---------- ---------------------------             10 Harry             11 Potter             12 Ganesha SQL> insert into a (select * from b); insert into a (select * from b)             * ERROR at line 1: ORA-00947: not enough values

Solution: 

1. Try to use same column range in both sub-query as well as in main query.

2. Delete extra columns from target table if not needed(Be careful).

Thanks for reading this post ! Please comment if you like this post ! Click on FOLLOW to get future blog updates !