INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Auto increment insert error

Auto increment insert error

(OP)
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).

CODE

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,

CODE

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

So I'm very confused...

Any help would be gratefully received.

RE: Auto increment insert error

(OP)

I have answered my own question...

Start by creating a sequence...

CODE

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

Then use the sequence in the insert...

CODE

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

Then delete the sequence...

CODE

DROP SEQUENCE TEMP_SEQ RESTRICT;

Hope someone else finds this helpful.

 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close