SqlBulkCopy - data sorting RRS feed

  • Question


    I am trying to insert data from a DataTable to the database using SqlBulkCopy.


    The data in the DatabTable is sorted. I want it to be inserted to the database in the same order as it is in DataTable .


    For small tables everything is fine. But when the number of rows in the table exceeds 5000, the data is getting inserted in random order. What can I do about it? Any parameters to change?


    bulkCopy = new SqlBulkCopy(connectionString);

                    bulkCopy.DestinationTableName = sTableName;

                    dtCopy.TableName = bulkCopy.DestinationTableName;

                    bulkCopy.BatchSize = dtCopy.Rows.Count;


    Thursday, January 29, 2009 8:10 PM

All replies

  • I do not think you can control it and there is no need for it if application designed properly. If application relies on physical order of rows in table, then it is definitely wrong approach since database server does not guarantee to return records in exact same order as they were inserted. If you need to receive sorted result, the best way would be to use ORDER BY clause of SQL query that returns data. Why is it important in your case?
    Val Mazur (MVP)
    Friday, January 30, 2009 2:58 AM
  • As Val indicated there is absolutely no reason to have the data sorted in the database. You can sort the data in your SQL statement when it is fetched from the database. If you don't already have one, a primary key for your table will be required.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Saturday, January 31, 2009 5:24 AM