Unique Constraint for Sequence

[whloo]

Hi, i am trying to use a single INSERT statement to insert multiple rows into a table.
But seems like it always give me primary key violation.

====================================================
INSERT INTO InterfaceFile(ID,CIID,OutputFieldName)
select seq_incr_interfacefile_1.nextval,'13',standardinterfacefile.outputfieldname from standardinterfacefile where standardinterfacefile.interfacename='AH'
=====================================================

i even tried to use trigger to solve it and it still giving me the same error
Please help. Thanks!

Regards,
weihann.

 

[whloo]

i just created a dummy table named interfacefileTemp with same structure except this time i never specified the ID as the primary key.
The same statement work perfectly ok
Any possible cause of this problem? Seems like it is due to primary constraint to the original InterfaceFile table?

 

[jbenson001]

Seems like you UDP seq_incr_interfacefile_1.nextval is returning the same value for each insert. I would suggest making your primay key an identity column. then in your INsert statment, leave out the ID. The id will be created with each insert automatically