How to insert more than multiple records (1500) in sql server using

    General discussion

  • Hi,

    We have a web-app where some check-boxes are used to select the number of records to be inserted. I am currently passing the records as xml string to back-end stored proc to insert the multiple records with a single db connection. Initially the expectation was around for 150 records max which the sql server VARCHAR string can accommodate in xml format. But for a particular record, the number of records have jumped to 1500. The sql server string will not be able to accommodate these many tags to insert multiple records. Is there any alternate way to do this?



    Wednesday, July 03, 2013 7:06 AM

All replies

  • If you are using SQL Server 2008 or later, the recommended way of passing array data to a stored procedure is by using table-valued parameters as described here:
    Wednesday, July 03, 2013 7:11 AM
  • The only alternative would be to use a less "verbose" Format then XML. CSV Formated string is common. But that has it's limits and does not support parameter syntax. So you have to sanitize what get's entered and would still eventually hit the NVARCHAR(MAX) limit.

    Table valued parameters is something for wich upgrading to SQL Server 2008 would be worth it. Having to parse XML in the Query is the most abysmal performance possible. With TVP's and Parameter Syntax or even Stored Procedures, the SQL Server could even use Execution Plans. Not to mention the speedup for not having to parse strings.

    Let's talk about MVVM:

    Wednesday, July 03, 2013 12:31 PM
  • You can use Bulk Copy rather than passing the records as an XML string, this would be more efficient on both the client and server side
    Wednesday, July 03, 2013 4:18 PM
  • How about having a Table Valued Parameter to insert bulk records?
    Wednesday, July 03, 2013 7:04 PM
  • We're using  SQL Server 2008 R2. Does this support TVP? I am trying to create a table type but it's giving me this error

    Incorrect syntax near the keyword 'AS'.

    CREATE TYPE LocationTableType AS TABLE
        ( LocationName VARCHAR(50)
        , CostRate INT )

    This is a MSDN sample that I am trying to experiment with. Please help!

    Friday, July 05, 2013 4:27 AM
  • Yes Server 2008 supports TVP. As the first one.

    But TVP's only work with stored Procedures. They are caleld TV-Parameters for a reason.

    I do not know where you try to execute that line, but it does not look like a StoredProcedure.

    Let's talk about MVVM:

    Friday, July 05, 2013 10:42 AM
  • I am just trying to create a table type here.  it gives me error. I will use it in a stored procedure after creating this table type. The below code is an MSDN example.

    USE AdventureWorks2012;
    /* Create a table type. */
    CREATE TYPE LocationTableType AS TABLE 
    ( LocationName VARCHAR(50)
    , CostRate INT );
    /* Create a procedure to receive data for the table-valued parameter. */
    CREATE PROCEDURE dbo. usp_InsertProductionLocation
        @TVP LocationTableType READONLY
        INSERT INTO AdventureWorks2012.Production.Location
            SELECT *, 0, GETDATE()
            FROM  @TVP;
    /* Declare a variable that references the type. */
    DECLARE @LocationTVP AS LocationTableType;
    /* Add data to the table variable. */
    INSERT INTO @LocationTVP (LocationName, CostRate)
        SELECT Name, 0.00
        FROM AdventureWorks2012.Person.StateProvince;
    /* Pass the table variable data to a stored procedure. */
    EXEC usp_InsertProductionLocation @LocationTVP;
    Friday, July 05, 2013 11:41 AM
  • Please find a sample TVP creation below:


    USE [LavanyaDeepak_Matrimonial]

    CREATE TYPE [dbo].[tvpEmailLog] AS TABLE(
        [MatchGenerateId] [int] NULL,
        [MatchGenerateDateTime] [datetime] NULL,
        [NumberOfAttempts] [int] NULL,
        [MatchGenerateUniqueId] [nvarchar](max) NULL


    And below is the stored procedure which uses this TVP:

    USE [LavanyaDeepak_Matrimonial]



    CREATE Procedure [dbo].[RecordMatches]
        @VARMatchLog tvpEmailLog READONLY
      Insert Into MatchLog (MatchGenerateId, MatchGenerateDateTime, NumberOfAttempts, MatchGenerateUniqueId)
      Select MatchGenerateId, MatchGenerateDateTime, NumberOfAttempts, MatchGenerateUniqueId from @VARMatchLog


    Sunday, July 07, 2013 11:21 PM