Auto increment insert error

[khsaunderson]

In the example below the first 3 columns are the unique identifier, and the 4th column contains the data. The TUNCODE is an auto incrementing field (hence it is commented out).

INSERT INTO MAIN_TABLE
(--TUNCODE,
TUNTYPE,
TUNCONT,
TUNTITLE)
SELECT 
'1', --TUNTYPE,
'0000000', --TUNCONT,
PHYSICAL_ISRC --TUNTITLE
FROM TEMP_TABLE;

However, every time I run the insert I get an error saying "SQL0803 - Duplicate key value specified." But I have checked and the TEMP_TABLE definitely doesn't contain any duplicates as found in the MAIN_TABLE. So I am beginning to think that it is picking up on the fact that the TUNTYPE and TUNCONT will be duplicated. But since the TUNCODE will be auto incremented, it shouldn't be duplicated. Eg,

TUNCODE  TUNTYPE  TUNCONT
000012   1        000000
000013   1        000000
000014   1        000000

So I'm very confused...

Any help would be gratefully received.

 

[khsaunderson]

I have answered my own question...

Start by creating a sequence...

CREATE SEQUENCE TEMP_SEQ  
START WITH 297016  
INCREMENT BY 1  
MINVALUE 297016  
NO MAXVALUE  
CYCLE  
CACHE 4;

Then use the sequence in the insert...

INSERT INTO MAIN_TABLE
(TUNCODE,
TUNTYPE,
TUNCONT,
TUNTITLE)
SELECT 
NEXTVAL FOR TEMP_SEQ,
'1', 
'0000000', 
PHYSICAL_ISRC
FROM TEMP_TABLE;

Then delete the sequence...

DROP SEQUENCE TEMP_SEQ RESTRICT;

Hope someone else finds this helpful.