5 Replies Latest reply on Nov 3, 2010 6:06 PM by sybrand_b

    ORA-00947: Not enough values

    806245
      I am trying to append Table A into Table B, where Table A has fewer columns than Table B. Is this no way that this operation can be carried out? Can't data insertion just take place between mathching columns, leaving additional columns in the target unfilled?
        • 1. Re: ORA-00947: Not enough values
          SomeoneElse
          You'll have to add nulls where appropriate.
          1 person found this helpful
          • 2. Re: ORA-00947: Not enough values
            Sure that is possible.
            Requirements:
            - Your insert statement reads
            insert into foo(col1, col2, col3) values (1,2,3)
            - the columns you don't mention are null allowed.

            Sloppy programming will result in ora-0947 and not reading documentation has resulted in yet another redundant question in this forum!

            ------------
            Sybrand Bakker
            Senior Oracle DBA
            • 3. Re: ORA-00947: Not enough values
              806245
              Thank you for the responses.

              Well, sometimes the documentation can raise more questions than answers. This is I guess the main reason for BBs.

              The error arises from a straight forward INSERT INTO SELECT statement, and I assumed that differences in numbers of values in columns should not cause a problem.

              The only thing I can think of is that one of the inserted columns is highlighted as a reserved term in my editor (SQL developer): TableA.Name (i.e. Name is in bold and highlighted in a colour)

              I don't understand the explanation of the error message.

              Edited by: 803242 on 03-Nov-2010 10:32
              • 4. Re: ORA-00947: Not enough values
                Chinar
                803242 wrote:
                Thank you for the responses.

                Well, sometimes the documentation can raise more questions than answers. This is I guess the main reason for BBs.

                The error arises from a straight forward INSERT INTO SELECT statement, and I assumed that differences in numbers of values in columns should not cause a problem.

                I don't understand the explanation of the error message.
                As you said in your case insert into  table1 select /*or some columns */ from tabl2* ,this error occur due to your table1 has x count columns but your second statement return y count columns which x>y therefore you get an error.
                In this case you can pass null value(if this column can be null) for this column as
                insert into table1 select null,null,a,b,c from tabl2
                1 person found this helpful
                • 5. Re: ORA-00947: Not enough values
                  The error arises from a straight forward INSERT INTO SELECT statement, and I assumed that differences in numbers of values in columns should not cause a problem.
                  Your assumption is incorrect.
                  Generally speaking not using
                  INTO <table>(<column>...)
                  is considered bad programming precisely because differences in numbers of values do cause problems.

                  ----------
                  Sybrand Bakker
                  Senior Oracle DBA