[b]Cannot insert duplicate key row error. Help???[/b]

[dobrios]

Cannot insert duplicate key row in object 'tarInvoiceLog' with unique index 'XAK1tarInvoiceLog'.
The statement has been terminated.

I get this error when I run insert statement within cursor ( I need to do line by line processing).

I checked for duplicates, there are none. If I run single insert, no errors.


-- create inv tran id
set @inv_tran_id = @inv_tran_no + '-IN'

-- generate inv key
exec dbo.spGetNextSurrogateKey 'tarInvoiceLog', @inv_key output


print 'inv tran id ' + @inv_tran_id
print 'inv_tran No ' + @inv_tran_no
print 'inv_key ' + cast(@inv_key as varchar(10))

-- insert into invoice log table
if @inv_key > 0
begin
insert into tarInvoiceLog
select
@inv_key, 'IBR'
, 0, @inv_date
, @inv_tran_id
, @inv_tran_no
, 3, @batch_type

Any ideas that might help me solve this problem?
Thanks

Steve

 

[mrdenny]

If there are truly no duplicates then your index may have become corrupt.

Try rebuilding it, or droping and recreating it.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[SQLBill]

That doesn't look like a curser script to me, so how can we figure out what's wrong? You provide the script for a single insert which you say does work.

I would check your cursor to see if it's actually 'looping' and reusing the same key.

-SQLBill

Posting advice: FAQ481-4875