[thelist] ASP: @@Identity

[Tab Alleman]

Problem: My @@Identity is experiencing a meltdown in the middle of my script
and I don't know why.

Background:  I've got a table in SQLServer7 that I import data to from
smaller tables.
When I import the data, I want to check to see if I already have that Item
in the database.
If the item is already there I want to (trust me when I say it has to be
this way) make a copy of the existing record, and update the copy with the
new data.  So I do this.

<psuedo ASP code>
DO WHILE NOT rsImport.EOF
	SELECT ID FROM MainTable WHERE TheImportData = TheMainTableData
	IF NOT EOF '(there are duplicates)
		DO WHILE NOT EOF 'so for each duplicate
			SELECT somefields FROM MainTable WHERE ID = (TheIDWeJustSelected)
			INSERT INTO MainTable (The Fields and Values we just Selected) 'make a
duplicate
			NewID = SELECT @@IDENTITY 'get the autonumber of the new record
			UPDATE MainTable SET (fields and values) WHERE ID = NewID
			MoveNext
		LOOP
	ELSE
		INSERT INTO MainTable (a new record)
	END IF
LOOP


Symptoms:  The script runs fine through several iterations of the outer
loop, and then it hits one where @@Identity doesn't return a value for some
reason, and it therefore explodes on the UPDATE statement.  Looking at the
data as it's written to the screen, I can only see one unusual thing about
the record it stops on everytime:  When it begins, it starts finding
duplicates in sequential order.. the first time it finds a duplicate with ID
#387, then 388, then 389...etc.  The one that it blows up on is the first
break in the sequence.. (it finds a dupe way back at 129).. but I don't see
what that would have to do with the @@Identity of the newly inserted record.

I've tried switching to all static cursors with the same results.. anybody
have any idea what could be causing this??

'preciate any thoughts.

Tab