Insert inside transaction inserts too few rows

  • 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.

  • 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

  • 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