SQL Error: ORA-00913: Too Many Values

Question

The two tables are identical in terms of table name, column names, data type, and size. These tables are located in separate databases, but I use current Login to hr user.

insert into abc.employees select * from employees where employee_id=100; 

I cannot use the original request from the corporate office.

 Error starting at line 1 in command: insert into abc.employees select * from employees where employee_id=100; Error at Command Line:1 Column:25 Error report: SQL Error: ORA-00913: too many values 00913. 00000 - "too many values" *Cause: *Action: 

Answer 1

You must specify the column names as shown below. This is good practice and will probably solve your problem.

 insert into abc.employees (col1,col2) select col1,col2 from employees where employee_id=100; 

EDIT

As you said, employees has 112 columns (sic!), Try the below to compare column columns

 select * from ALL_TAB_COLUMNS ATC1 left join ALL_TAB_COLUMNS ATC2 on ATC1.COLUMN_NAME = ATC1.COLUMN_NAME and ATC1.owner = UPPER('2nd owner') where ATC1.owner = UPPER('abc') and ATC2.COLUMN_NAME is null AND ATC1.TABLE_NAME = 'employees' 

and you have to update your tables to have the same structure.

Answer 2

If you have 112 columns in one table and you want to insert data from the source table, you can do it like

 create table employees as select * from source_employees where employee_id=100; 

Or from sqlplus do as

 copy from source_schema/password insert employees using select * from source_employees where employee_id=100; 

Answer 3

The message 00947 indicates that the record you are trying to send to Oracle does not have one or more columns that were included during table creation. Message 00913 indicates that the record you are trying to send to Oracle contains more columns than were included at the time the table was created. You just need to check the number of columns and their type in both tables i.e. tables that participate in SQL.

Answer 4

It works great for me

 insert into oehr.employees select * from employees where employee_id=99 

I'm not sure why you get the error. The nature of the error code you created is the columns that do not match.

One good approach is to use the @Parodo answer provided by

Answer 5

it's a bit late .. but I saw that this problem occurs when you want to insert or delete one row from / to DB, but u put / pull is more than one row or more than one value,

For example:

you want to delete one row from the database with a specific value, such as the identifier of the element, but you requested a list of identifiers, then you will come across the same exception message.

Sincerely.