none
SqlBulkcopy sortorder RRS feed

  • Question

  • Hi All,

    Is there an option to specify that the data is sorted in the dataset when using SqlBulkCopy. I am observing in SQL execution plans that there is an sort overhead when inserting in destination table.

    Dataset ds = new Dataset();

    .....

    ....

     using (var sqlBulk = new SqlBulkCopy("Server = <sever>.database.windows.net; Database = db1; User Id = adn; Password =<pwd>;"))
                {
                    sqlBulk.DestinationTableName = "Employees";
                    sqlBulk.WriteToServer( ds);
                }

    TIA

    Sunday, February 3, 2019 2:10 AM

Answers

  • Hi GKS001,

    Based on my testing I don't see any guarantee that the order in the SQL Server destination table will be the same as the source. 

    I would suggest that you could create a temp field which save the order in it. then you could fetch the records via this temp field.

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by GKS001 Friday, March 1, 2019 8:27 PM
    Monday, February 4, 2019 2:33 AM
    Moderator
  • No guarantee of order. The other posters suggestion to use a temp field seems reasonable.

    Unrelated to your question. To get better performance you should send the records to SqlBulkCopy immediately, and let the class use its own buffering and batching. SqlBulkCopy can work with an IDataReader. All ADO.NET data readers implement this interface, so you can push data that you read from any data reader to SqlBulkCopy.


    • Marked as answer by GKS001 Friday, March 1, 2019 8:27 PM
    • Edited by William Xifaras Friday, March 1, 2019 9:08 PM
    Friday, March 1, 2019 8:18 PM

All replies

  • Hi GKS001,

    Based on my testing I don't see any guarantee that the order in the SQL Server destination table will be the same as the source. 

    I would suggest that you could create a temp field which save the order in it. then you could fetch the records via this temp field.

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by GKS001 Friday, March 1, 2019 8:27 PM
    Monday, February 4, 2019 2:33 AM
    Moderator
  • No guarantee of order. The other posters suggestion to use a temp field seems reasonable.

    Unrelated to your question. To get better performance you should send the records to SqlBulkCopy immediately, and let the class use its own buffering and batching. SqlBulkCopy can work with an IDataReader. All ADO.NET data readers implement this interface, so you can push data that you read from any data reader to SqlBulkCopy.


    • Marked as answer by GKS001 Friday, March 1, 2019 8:27 PM
    • Edited by William Xifaras Friday, March 1, 2019 9:08 PM
    Friday, March 1, 2019 8:18 PM