locked
bulk updates/inserts in sql server 2005 RRS feed

  • Question

  • I sync two sql server databases (sql server 2005, sql server 2008) using sync framework 2.1

    Sql server 2008 support bulk inserts/updates SP. I can enable them during provisioning.

    I heard sql server 2005 edition don't support bulk inserts/updates. But I need the bulk inserts/updates option in sql server 2005 also to decrease round trips when changedata is more

    Is there any option to do so (enabling bulk inserts/updates in sql server 2005)


    ess


    • Edited by saitrips Friday, October 26, 2012 2:29 AM
    Friday, October 26, 2012 2:19 AM

All replies

  • the bulk procedure uses Table Valued Parameters (User Defined Table Types) and MERGE statements which are not available on 2005.

    assuming you find a way to update by bulk on SQL 2005, you will have to write a custom provider to send the changes in bulk from code to SQL Server.

    Monday, October 29, 2012 12:27 PM
  • Currently I am using batching for large data transfers.

    Wht is difference between batching versus bulk inserts/updates SP in sync framework 2.1

    could you please help me to differentiate the both concepts. 


    ess

    Tuesday, October 30, 2012 2:47 PM
  • Batching is set using the MemoryDataCacheSize property. it's used when enumerating changes so that the memory usage when enumerating changes is lessen and  so that changes can be sent to the remote provider in chunks. (Dataset serialization is an expensive operation)

    The bulk operations has something to do with how changes are applied to SQL Server itself. say you have a 100 rows to apply, non-bulk would send 100 commands to SQL Server. Bulk would only need to send one command containing all the records to be applied.

    batching is for enumerating and sending changes, bulk operations for applying to SQL server

    Wednesday, October 31, 2012 6:21 AM