SQL Error ORA-00913 too many values

Question

Two tables are identical in terms of table name  column names  datatype and size  These tables are located in separate databases  but I am use to  current Log in in hr user   insert into abc employees select   from employees where employee id 100     I can not give use original query from 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

User · Answer

You should specify column names as below. It's good practice and 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 to run below select to compare both tables' 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 than you should upgrade your tables to have the same structure.

User · Answer

If you are having 112 columns in one single table and you would like to insert data  from source table  you could do as  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

User · Answer

For me this works perfect  insert into oehr employees select   from employees where employee id 99   I am not sure why you get error  The nature of the error code you have produced is the columns didn t match    One good approach will be to use the answer  Parodo specified

User · Answer

The 00947 message indicates that the record which you are trying to send to Oracle lacks one or more of the columns which was included at the time the table was created  The 00913 message indicates that the record which you are trying to send to Oracle includes more columns than were included at the time the table was created  You just need to check the number of columns and its type in both the tables  ie the tables that are involved in the sql

User · Answer

this is a bit late   but i have seen this problem occurs when you want to insert or delete one line from to DB but u put pull more than one line or more than one value     E g    you want to delete one line from DB with a specific value such as id of an item but you ve queried a list of ids then you will encounter the same exception message    regards

[sql] SQL Error: ORA-00913: too many values

Examples related to sql

Examples related to oracle