Oracle not enough values error

Question

I want to insert one table to another table. Just the difference is in another table One column is Auto increment, and the column name is SEQ_NO

I tried with the below query, but is giving error as

ORA-00947: not enough values

Kindly suggest why the query is giving error

you are still missing one column if i see it correctly. You include a Date, but you are still missing the `HISTSEQ_NO` column — SomeJavaGuy, Apr 05 '16 at 08:59
Specify columns, i.e. `INSERT INTO t (c1, c2, c3, ...) SELECT ca, cb, cc, ... FROM ...`. Note, no parentheses needed for the SELECT. — jarlh, Apr 05 '16 at 08:59
@KevinEsche: i want to insert `HISTSEQ_NO` auto incremently.. i dont think I need date for that. — Nad, Apr 05 '16 at 09:00
@jarlh: `HISTSEQ_NO` is my auto incremented column, and rest all the columns will come..how to handle that `HISTSEQ_NO` part ? — Nad, Apr 05 '16 at 09:02
Simply don't list that column in the insert-column list, and don't select that value. — jarlh, Apr 05 '16 at 09:04
@jarlh: okay. i am trying the way u suggested and checking it — Nad, Apr 05 '16 at 09:06
@jarlh: i tried your way, but my `HISTSEQ_NO` is going blank. It should go `1`, `2`, `3`,... — Nad, Apr 05 '16 at 09:10
Oops, put that column back to the insert-column list, and select current_timestamp (or similar) in that columns position. — jarlh, Apr 05 '16 at 09:13

Aleksej · Accepted Answer · 2016-04-05T09:16:25.883

You need to explicitly write every column you need to handle in your insert, excluding those having some autoincrement, default, ...

This is however a good idea, even if you don't need it, because it gives you more control and a more readable code:

insert into xxacl_pn_project_link_h(
                                    HISTSEQ_NO         ,
                                    HIST_DATE          ,
                                    MKEY               ,
                                    PROJECT_ID         ,
                                    PROJECT_NAME       ,
                                    DIRECT_BOOKING     ,
                                    NON_ORACLE_PROJECT ,
                                    ATTRIBUTE1         ,
                                    ATTRIBUTE2         ,
                                    ATTRIBUTE3         ,
                                    ATTRIBUTE4         ,
                                    ATTRIBUTE5         ,
                                    LAST_UPDATE_DATE   ,
                                    DELETE_FLAG        ,
                                    CREATION_DATE      ,
                                    CREATED_BY         ,
                                    LAST_UPDATE_LOGIN  
                                    )
select HISTSEQ_NO_sq.nextVal,/*HISTSEQ_NO        */ 
       sysdate              ,/*HIST_DATE         */
       MKEY                 ,/*MKEY              */
       PROJECT_ID           ,/*PROJECT_ID        */
       PROJECT_NAME         ,/*PROJECT_NAME      */
       DIRECT_BOOKING       ,/*DIRECT_BOOKING    */
       NON_ORACLE_PROJECT   ,/*NON_ORACLE_PROJECT*/
       ATTRIBUTE1           ,/*ATTRIBUTE1        */
       ATTRIBUTE2           ,/*ATTRIBUTE2        */
       ATTRIBUTE3           ,/*ATTRIBUTE3        */
       ATTRIBUTE4           ,/*ATTRIBUTE4        */
       ATTRIBUTE5           ,/*ATTRIBUTE5        */
       LAST_UPDATE_DATE     ,/*LAST_UPDATE_DATE  */
       DELETE_FLAG          ,/*DELETE_FLAG       */
       CREATION_DATE        ,/*CREATION_DATE     */
       CREATED_BY           ,/*CREATED_BY        */
       LAST_UPDATE_LOGIN     /*LAST_UPDATE_LOGIN */
FROM xxacl_pn_project_link l
 WHERE l.mkey = '1'

To handle your HISTSEQ_NO field, giving that it has no autoincrement logic, you can use a sequence:

create sequence HISTSEQ_NO_sq start with 1

so HISTSEQ_NO has no auto increment logic; you can create a sequence and use it for the insert; just edited my asnwer — Aleksej, Apr 05 '16 at 09:16
yup, done. now its working. One thing here. Do we need to create a `sequence` for every **auto incremented** column ? — Nad, Apr 05 '16 at 09:19
Having a sequence for each field is not stricly necessary, but it would be more readable; however, the decision may depend on many other things; [here](http://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle) you find something more oh how to handle autoincrement fields in Oracle — Aleksej, Apr 05 '16 at 09:23

score 0 · Answer 2 · answered Apr 05 '16 at 09:07

0

You're missing the value for your history specific column HISTSEQ_NO. Most probably you want to use a sequence to generate that id/number.

answered Apr 05 '16 at 09:07

Fabian Barney

14,219
5
40
60

score 0 · Answer 3 · answered Apr 05 '16 at 09:08

Specify column names. And leave the column HISTSEQ_NO. If it is auto increment then it will be added automatically while inserting.

 INSERT INTO xxcus.xxacl_pn_project_link_h (
    HIST_DATE
    ,MKEY
    ,PROJECT_ID
    ,PROJECT_NAME
    ,DIRECT_BOOKING
    ,NON_ORACLE_PROJECT
    ,ATTRIBUTE1
    ,ATTRIBUTE2
    ,ATTRIBUTE3
    ,ATTRIBUTE4
    ,ATTRIBUTE5
    ,LAST_UPDATE_DATE
    ,DELETE_FLAG
    ,CREATION_DATE
    ,CREATED_BY
    ,LAST_UPDATE_LOGIN
    )
SELECT SYSDATE
    ,MKEY
    ,PROJECT_ID
    ,PROJECT_NAME
    ,DIRECT_BOOKING
    ,NON_ORACLE_PROJECT
    ,ATTRIBUTE1
    ,ATTRIBUTE2
    ,ATTRIBUTE3
    ,ATTRIBUTE4
    ,ATTRIBUTE5
    ,LAST_UPDATE_DATE
    ,DELETE_FLAG
    ,CREATION_DATE
    ,CREATED_BY
    ,LAST_UPDATE_LOGIN
FROM xxcus.xxacl_pn_project_link l
WHERE l.mkey = '1'

getting error as `ORA-01400: cannot insert NULL into ("XXCUS"."XXACL_PN_PROJECT_LINK_H"."HISTSEQ_NO")` — Nad, Apr 05 '16 at 09:16
You mentioned in comments that `HISTSEQ_NO` `is my auto incremented column` — Utsav, Apr 05 '16 at 09:24

Oracle not enough values error

3 Answers3