You must Sign In to post a response.
  • Category: ASP.NET

    How to add large number of records to temp table

    Hi All,
    I am working on a module in which there is a scenario in which I had to add more than 25,000 records to SQL server temp table from VB.NET list using inline SQL queries. Since this is a performance improvement module and it's very complex I've some restrictions to not use stored procedure for the same. If I try to insert values to temp table or table variable using insert into or select into SQL server throws error stating that it can not add more than 1000 rows. So is there any best optimized solution for this kind of problem which can be done using minimal line of code and it will be efficient also. Please let me know if you have any idea on this. Thanks in Advance.

    Environment-Visual Studio 2010,VB.NET,SQL Server 2012

    Regards,
    Ashutosh
  • #763893
    Temp table is nothing
    1. Creating table
    2. Add data
    3. Delete the table

    Correct?

    Why can n't you try to do it using normal table?.
    Once you have done all your calculation, delete that table.

    By Nathan
    Direction is important than speed

  • #763895
    Nathan,
    Thanks for the answer. Actually I've tried with normal table, table variable and temp table still it's not working.

  • #763908
    Hai Ashutosh,
    You can't insert more than 1000 records at once using an insert statement- whether it is normal table or temp table. The internal behavior of the normal and temp table is same in this regards.
    So you need to change your approach here and use either the bcp command or Bulk Copy command to do so. Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #763929
    Have you tried SqlBulkCopy Class?
    It help you to efficiently bulk load a SQL Server table with data from another source, I have tried with more than 50K records
    I think you can prepare a datatable with 25K rows and then you can directly upload it
    I have below snippet with me
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
    {
    bulkCopy.DestinationTableName =
    "dbo.BulkCopyDemoMatchingColumns";

    try
    {
    // Write from the source to the destination.
    bulkCopy.WriteToServer(newProducts);
    }
    catch (Exception ex)
    {
    Console.WriteLine(ex.Message);
    }
    }

    // Perform a final count on the destination
    // table to see how many rows were added.
    long countEnd = System.Convert.ToInt32(
    commandRowCount.ExecuteScalar());
    Console.WriteLine("Ending row count = {0}", countEnd);
    Console.WriteLine("{0} rows were added.", countEnd - countStart);
    Console.WriteLine("Press Enter to finish.");
    Console.ReadLine();
    }
    May you can try below links
    http://www.codeproject.com/Articles/18418/Transferring-Data-Using-SqlBulkCopy

    Thanks
    Koolprasd2003
    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]


Sign In to post your comments