Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    2

    Unanswered: Insert into existing table

    Hi all,

    I have created a table (GP2_CurrentOrders) from an exsiting ones. Now I'd like to append the orders for the previous month to the GP2_CurrentOrders table. However I always get the following error message:

    ERROR at line 1:
    ORA-00947: not enough values

    I know that I have 10 columns in the GP2_CurrentOrders table and I need to insert two values in it. My joins and tables are probably not correct. Can anybody help me with that ?

    Thank you.


    CREATE TABLE GP2_CurrentOrders AS
    SELECT CustID, CustNameLast, OrderID,
    OrderDate,
    PartID, PartDesc, Quantity, QuotedPrice,
    (QuotedPrice * SalesTaxRate) + QuotedPrice AS "ExtendedPrice",
    NVL(SalesTaxRate, 0) AS "SalesTaxRate"
    FROM GP2_Customer INNEr JOIN GP2_Order USING (CustID) INNER JOIN
    GP2_OrderLine USING (OrderID) INNER JOIN
    GP2_Part USING (PArtID)
    WHERE extract (Month from OrderDate) = extract (Month from SysDate);

    --insert
    INSERT INTO GP2_CurrentOrders
    SELECT OrderID, CustID
    FROM GP2_CUSTOMER INNER JOIN GP2_ORDER USING (CUSTID) INNER JOIN
    GP2_OrderLine USING (OrderID) INNER JOIN
    GP2_Part USING (PartID)
    WHERE extract (Month from OrderDate) = extract (Month from Sysdate) -1;

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Any "insert into .." must supply exactly the right number of values as per the number of columns in the destination table.

    Ie,

    insert into sometable (select col1, col2 from othertable...) should include nulls for any other columns in SOMETABLE.

    Simply pass nulls into the columns you don't have values for.

    Hth
    Bill

  3. #3
    Join Date
    Nov 2003
    Posts
    2
    OK, I have passed 8 null columns in my Insert Table.
    Now I get another "interesting" error message:

    ERROR at line 2:
    ORA-01401: inserted value too large for column

    The desc from the table:

    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    CUSTID NOT NULL CHAR(5)
    CUSTNAMELAST NOT NULL VARCHAR2(30)
    ORDERID CHAR(6)
    ORDERDATE NOT NULL DATE
    PARTID CHAR(5)
    PARTDESC NOT NULL VARCHAR2(30)
    QUANTITY NOT NULL NUMBER(5)
    QUOTEDPRICE NOT NULL NUMBER(6,2)
    ExtendedPrice NUMBER
    SalesTaxRate NUMBER

    Unfortunately it does not specifically say which column a problem has.

    Any suggestions ?

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    No specific suggestions, but you probably can work it out through a process of elimination.

    Try inserting the column values one at a time with the rest as nulls, on completion rollback and try again with the next column.

    Hth
    Bill

  5. #5
    Join Date
    Sep 2003
    Location
    NE Florida w/ view of co-workers
    Posts
    32
    It's good practice to include in any insert statement the specific columns that are being inserted, i.e., insert into table (col1, col2) select col1, col2 ... This way if a column is ever added or the table is rebuild and columns are re-ordered, your insert statement will not fail.

    Tips on finding the wrong length column:
    - most likely it is a string datatype
    - use max(length(column)) on each string column to try and find the one or two records that probably are too long
    - double check the columns you are selecting and inserting to just in case it is a numeric datatype

    Good luck!

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    Why not specify each column in your insert statement?

    insert into table1 (col1, col2, col3)
    select a, b, c from table2;

    also, to solve your other problem, change your select statement columns and use the LENGTH function to check everything you are loading.

    just run the select statement and order by length or something.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •