I need to store about 25k rows worth of data into a single table about once a week. To achieve this, I'm creating an object (created from parsing XML), and sending the data within that object to a stored procedure. There's an object containing data for each row in the table.

Here's the stored procedure:

ALTER PROCEDURE [dbo].[BankImport] 
    -- Add the parameters for the stored procedure here
    @bankName nvarchar(50) = null,
    @shortBankName nvarchar(50) = null,
    @branchName nvarchar(50) = null,
    @sortCode int,
    @addresseeName nchar(60) = null,
    @postalName nchar(60) = null,
    @addressLine1 nchar(100) = null,
    @addressLine2 nchar(80) = null,
    @cityOrTown nchar(50) = null,
    @areaOrCounty nchar(60) = null,
    @postCode nchar(12) = null,
    @fasterPayments nchar(10) = null,
    @directDebits nchar(10) = null,
    @chaps nchar(10) = null,
    @chequeCreditClearing nchar(10) = null

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.

    IF EXISTS (SELECT 1 FROM [dbo].[banks] WHERE sortCode = @sortCode)

        UPDATE [dbo].[banks] 
        SET @bankName = bankName,
            @shortBankName = shortBankName,
            @branchName = branchName,
            @sortCode = sortCode,
            @addresseeName = addresseeName,
            @postalName = postalName,
            @addressLine1 = addressLine1,
            @addressLine2 = addressLine2,
            @cityOrTown = cityOrTown,
            @areaOrCounty = areaOrCounty,
            @postCode = postCode,
            @fasterPayments = fasterPayments,
            @directDebits = directDebits,
            @chaps = chaps,
            @chequeCreditClearing = chequeCreditClearing
        WHERE sortCode = @sortCode


        INSERT INTO [dbo].[banks] VALUES (


It works okay on smaller datasets, but the full-size contains each branch for each bank in the UK. It manages to get through to about 15k rows before giving this error:

Thread was being aborted.

Is this likely to be more of a time-out issue, or inefficiency, or is there something else that I'm missing?

  • Similar to: Question1, Question2 on SO. This is mostly not caused by the database, but by how you work with connections to the db. Some suggestion, you should try the MERGE statement instead of the Update/Insert couple. – Marian Nov 22 '12 at 12:13
  • Thanks for that, @Marian, I'll look into the MERGE statement! – edparry Nov 22 '12 at 13:32
  • This code is prone to race conditions, which may be causing the problem depending on the data and how the data is sent to the stored procedure -- see here. If you can change to MERGE, that would be better than fixing the locking issues with this code. – Jon Seigel Nov 22 '12 at 14:25
  • 1
    @JonSeigel note that MERGE is not magic; it is also prone to race conditions (though certainly safer than the code above). It also isn't necessarily going to solve the problem if you call the stored procedure 25,000 times and it is simply using MERGE each time instead of UPDATE/INSERT. :-) – Aaron Bertrand Nov 22 '12 at 17:32
  • 1
    @Aaron: Interesting, thanks for the link, I wasn't aware of that -- I figured that kind of thing would be handled automatically since it's a "single statement." The OP gave no indication of the how the application works, so we can't really make the assumption that it's single threaded. (If it is, then I agree with you.) I tend to believe that threading issues and errors are more likely application-related than SQL Server-related, which is why I went down that path in my comment. – Jon Seigel Nov 22 '12 at 17:57

Strongly recommend you look into Table-Valued Parameters and treat your insert/update as a set (using MERGE probably) instead of calling a singleton stored procedure 25,000 times. You can skip a lot of steps here (in C# you can just stuff your 25K rows into a DataTable, for example, making it easy to pass to a stored procedure in a single call), and reduce overhead significantly.

If you create a type for your bank rows:

  SortCode int PRIMARY KEY,
  BankName nvarchar(50),
  ShortBankName nvarchar(50),
  BranchName nvarchar(50),
  AddresseeName nchar(60),
  PostalName nchar(60),
  AddressLine1 nchar(100),
  AddressLine2 nchar(80),
  CityOrTown nchar(50),
  AreaOrCounty nchar(60),
  PostCode nchar(12),
  FasterPayments nchar(10),
  DirectDebits nchar(10),
  Chaps nchar(10),
  ChequeCreditClearing nchar(10)

Your procedure then might look like this:

  @BankRow dbo.BankRow READONLY

  MERGE dbo.Banks AS b
    USING @BankRow AS tvp
    ON tvp.SortCode = b.SortCode
    UPDATE SET BankName = tvp.BankName,
               ShortBankName = tvp.ShortBankName,
    INSERT (SortCode, BankName, ShortBankName, ...)
    VALUES (tvp.SortCode, tvp.BankName, tvp.ShortBankName, ...)

So now you can pass your DataTable into the stored procedure once instead of calling the singleton stored procedure 25,000 times. You'll have to add the other columns where I put ... and you'll also have to adjust your C# code to work with a TVP.

I wrote up a quick article on TVPs here, including a quick example on C# syntax:


And Erland's article is useful as well:


| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.