Inserting into an ORACLE table that has an auto increment sequence from another table?-CodePudding

I have an ORACLE database with two tables, one is an import of old data and another is "address"

I have set up the "address" table to auto increment using the following code:

CREATE SEQUENCE AddressID 
MINVALUE 1 START WITH 1 
CACHE 10;

I have to insert all the values from the same named columns from a table called "IMPORT_EMPLOYEE" but I can't get it to do that. This is what I have tried so far:

  INSERT INTO ADDRESS (ADDRESS.NEXTVAL,StreetAddress, City, Province, PostalCode)
SELECT StreetAddress, City, Province, PostalCode
FROM IMPORT_EMPLOYEE;

This returns the following error and I suspect this is because of the address.nextval:

Error at Command Line : 1 Column : 13 Error report - SQL Error: ORA-00947: not enough values 00947. 00000 - "not enough values" *Cause:
*Action:

Any help would be greatly appreciated!

CodePudding user response：

You put the sequence to the wrong place; should've been in select, while insert into should contain the "target" column (let's presume its name is id):

INSERT INTO address (
    id,
    streetaddress,
    city,
    province,
    postalcode
)
    SELECT
        address.NEXTVAL,
        streetaddress,
        city,
        province,
        postalcode
    FROM
        import_employee

Error you got - if everything else was OK - means that you're inserting 4 values into 5 columns, and that's a mismatch.