INSERT INTO TABLE CONTAINING IDENTITY COL USING SELECT FROM SECOND TABLE

General
Highlighted
Junior Supporter

INSERT INTO TABLE CONTAINING IDENTITY COL USING SELECT FROM SECOND TABLE

I'm getting a 3812 error when attempting to insert into a table containing an identitiy column using a select from a secnd table.

Basically this is what I have done; 

I'd appreciate any suggestions..... thanks

 

CREATE SET TABLE TB1 ,NO FALLBACK ,

     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
--        TB1ID INTEGER GENERATED BY DEFAULT AS IDENTITY
       TB1ID INTEGER GENERATED ALWAYS AS IDENTITY
             (  START WITH 1
                INCREMENT BY 1
                MAXVALUE 10000
             ),
        ID_DESC  VARCHAR(100),
        TB1COL1 CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC,
        TB1COL2 DATE FORMAT 'YYYY-MM-DD'
      ) PRIMARY INDEX (TB1COL2) ;
      
CREATE SET TABLE TB2 ,NO FALLBACK ,

     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
        TB2COL1 CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC,
        TB2COL2 DATE FORMAT 'YYYY-MM-DD'
      ) PRIMARY INDEX (TB2COL2) ;  

INSERT INTO TB2 (TB2COL1, TB2COL2) VALUES('ABCD', DATE) ;  

      
 INSERT INTO TB1
     SELECT ' ', TB2COL1, TB2COL2 FROM TB2 ;   <<<<<<<<<<<<<<<<<<<<   3812

1 REPLY
Junior Contributor

Re: INSERT INTO TABLE CONTAINING IDENTITY COL USING SELECT FROM SECOND TABLE

Your target table has four columns and your Select returns three columns, of course this fails.

You must use a column list:

INSERT INTO TB1 (ID_DESC, TB1COL1, TB1COL2) 
SELECT ' ', TB2COL1, TB2COL2 FROM TB2

Btw, your MAXVALUE is way too low, for an Insert/Select each AMP will request a range of values and the default batch size is 100000.