June 14, 2005 at 3:02 am
I'm having problems with a stored proc. I have an insert statement that runs inside a transaction. The basic problem is that the insert only inserts a percentage of the rows it should insert. There is no error, it just doesn't insert all the rows. If I run the sp again, it then inserts the rows it missed the first time round.
Ok, some more detail. The sp looks similar to the following. I've added some comments to show how many records are added at each stage. These are not exactly the same as my process (as it varies), but are representative.
Begin Transaction
-- Create keys for TypeA records
-- So, for each SystemID, UniqueID and TypeID inserted into KeyGenerator
-- a new Identity value is created - this is then used as a PK later on
Insert Into KeyGenerator(SystemID, UniqueID, TypeID)
Select @SystemID, src.UniqueID, @TypeAId
From MyTable src
Where src.Type = 'TypeA'
-- Correctly inserts 100 rows
-- Create keys for TypeB records
Insert Into KeyGenerator(SystemID, UniqueID, TypeID)
Select @SystemID, src.UniqueID, @TypeBId
From MyTable src
Where src.Type = 'TypeB'
-- Correctly inserts 200 rows
-- Delete from MyMaster table
Delete From MyMaster
Where SystemID = @SystemID
-- Correctly deletes 300 rows - assume 300 rows were currently in MyMaster
-- Insert TypeA into MyMaster
Insert Into MyMaster(Forename, Surname, PrimaryKeyID)
Select src.Forename, src.Surname, keygen.PrimaryKeyID
From MyTable src
Inner Join KeyGenerator keygen
On src.UniqueID = keygen.UniqueID
And keygen.SystemID = @SystemID
And keygen.TypeID = @TypeAId
Where src.Type = 'TypeA'
-- Inserts only 40 rows - should insert 100 rows
-- NB: This varies - sometimes it inserts a few more/less than 40
-- Insert TypeB into MyMaster
Insert Into MyMaster(Forename, Surname, PrimaryKeyID)
Select src.Forename, src.Surname, keygen.PrimaryKeyID
From MyTable src
Inner Join KeyGenerator keygen
On src.UniqueID = keygen.UniqueID
And keygen.SystemID = @SystemID
And keygen.TypeID = @TypeBId
Where src.Type = 'TypeB'
-- Correctly inserts only 200 rows
Commit Transaction
A 'select count' confirms that MyTable now contains 240 rows, when it should contain 300.
If I re-run the sp it effectively inserts the remaining 60 rows. Note, that as the process deletes all rows from MyMaster before re-inserting them, the second run of the sp inserts all 300 rows; it doesn't just insert an extra 60 rows.
If I run the sp without the Transaction commands it correctly inserts all rows on the first attempt.
Like the Aussie cricket team yesterday - I'm stumped.
June 14, 2005 at 9:11 am
It is kind of hard to give you an explanation without further data but I can tell you this. Every time you perform a DML you have to immediately check for @@error (I don't see that in your code, did you suppressed it?) then rollback the whole thing if anything does not work.
Last and not least if that still does not give you an answer, can you provide some test data to see if it can be duplicated!
Cheers,
* Noel
June 14, 2005 at 9:19 am
I do check for @@Error after Insert/Update statements, it's just missing from here for brevity. If there are any errors, then I issue a rollback.
I can't provide the data I use. As for other test data, it would need to be a large amount to see this problem. For example, it happens when I am inserting upwards of 1,000,000 rows, but with lesser amounts I don't get the problem.
Hm, there's a thought: I could run some tests to see roughly how many records it will handle correctly. From memory, it inserts roughly 200,000 of the 550,000 it should insert.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply