Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2006
    Posts
    31

    Unanswered: Loading a table with GENERATED BY DEFAULT AS IDENTITY

    Hi all,

    I am trying to load the data from EMPLOYEE table into EMPLOYEE_NEW table.Both the tables have the below structure.

    EMPLOYEE (
    EMPNO BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH +1 INCREMENT BY +1 MINVALUE +1 MAXVALUE +9223372036854775807 NO CYCLE CACHE 20 NO ORDER ),
    LAST NAME NOT NULL,
    FIRST NAME NOT NULL)

    load cursor statements.

    DECLARE CURSOR1 CURSOR FOR SELECT * FROM EMPLOYEE;
    LOAD FROM CURSOR1 OF CURSOR MODIFIED BY IDENTITYOVERRIDE INSERT INTO EMPLOYEE_NEW;

    error :
    SQL3526N The modifier clause "IDENTITYOVERRIDE" is inconsistent with the
    current load command. Reason code: "5".
    Reason :
    The IDENTITYOVERRIDE file type modifier cannot be used when loading a table with a GENERATED BY DEFAULT identity column.

    What is the best solution to load the data into EMPLOYEE_NEW table and to retain the EMPNO from the EMPLOYEE table.

    Thanks in advance.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If you read the documentation then you would know that IDENTITYOVERRIDE is for GENERATED ALWAYS, not GENERATED BY DEFAULT. If you just remove that clause from the load, it should work the way you want it to.

    Andy

  3. #3
    Join Date
    Nov 2006
    Posts
    31

    Thanks

    Works great..Thanks

Posting Permissions

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