missing sql rows

MargeS

I am testing a script that inserts data into a sql DB. Sometimes it works - sometimes it doesn't (not a problem, I'm testing!)

When the script fails no records are add to the database. Then the script succeeds a record gets added. But I end up missing many rows, for example the last successful row could be id= 5 then it jumps to id=9.

Where do the missing rows go if nothing gets added to the DB? :rolleyes:

Tom Dunne

It's almost impossible to guess without more details.

It could be a flaw in your logic, but without seeing the script, it is hard to say.

Adam

Precisely. This is impossible to diagnose without the script your using.

Ginger

Tis a very simple thing really

When you are using SQL Server and the Auto increment property on an ID field, when you prepare to insert it takes the next availible number. If the insert fails it wont be reused. So when the next insert happens it takes the next one etc etc

So for example say you have 1 success 3 fails 1 success starting at ID 1

1 - Success
2 - Fail
3- Fail
4- Fail
5- Success

So your table no has numbers 1 & 5 in the db...

You can reseed it but its a standard part of SQL Server

MargeS

Txs Ginger - so the ID is reserved before the insert happens! Maybe generating random userID's is a better way to go.

Ginger

Not really.. just leave it as is .. less work and you know the numbers will be unique..

@SESSION_IDENTITY)

No real need to worry about it