locked
DROP And CREATE, BULK LOADing and Change Tracking RRS feed

  • Question

  • I am updating a MS SQL 2008 database tables every day from external data. This adds each day one new

    row in every table.

     

    The tables are very big and recreated every day. The only difference between yesterday and today data is today data row.

    Because tables are big I am using DROP And CREATE and then with SSIS package BULK LOADing the data

    in the tables.

     

    Need to create occasionally connected appilcation to synchronize with this database.

     

    My question is does Change Tracking tracks these changes? Or I have ot use

    Change Data Capture?

     

    Thanks in advance,

     

     

     

    • Moved by Max Wang_1983 Thursday, April 21, 2011 6:12 PM forum consolidation (From:SyncFx - Technical Discussion [ReadOnly])
    Monday, August 18, 2008 7:33 AM

Answers

  • From the description you provide regarding how you update your database tables with changes from external data it does not sound like you will be able to perform efficient sync with the database for your occasionally connected application.  If I understand correctly, each day there is a new row of data that needs to be added to one or more tables.  The database tables are updated by reloading them with a complete copy of the data from the external source - correct?  Presumably you have to do this because the external source can't send you just the new data?

     

    With the current design of dropping and recreating your tables, then both Change Tracking and Change Data Capture (CDC) are of no real use.  With both these features, when you drop the tables the change information for the table is lost; when you reload the tables, then both features will report every single row as a change!

     

    I think you really need to find a way to incrementally update your database tables and avoid the drop and recreate.  If you can simply add a row to your tables each day then both Change Tracking and CDC will be able to identify change efficiently for you and your occasionally connected application will be able to sync incremental changes.  If the external source can't provide you with only the new data, maybe you can query the database before updating or inserting data?

     

    Hope that helps!

     

    Regards, Mark

     

    Tuesday, September 16, 2008 6:07 PM
    Moderator

All replies

  • From the description you provide regarding how you update your database tables with changes from external data it does not sound like you will be able to perform efficient sync with the database for your occasionally connected application.  If I understand correctly, each day there is a new row of data that needs to be added to one or more tables.  The database tables are updated by reloading them with a complete copy of the data from the external source - correct?  Presumably you have to do this because the external source can't send you just the new data?

     

    With the current design of dropping and recreating your tables, then both Change Tracking and Change Data Capture (CDC) are of no real use.  With both these features, when you drop the tables the change information for the table is lost; when you reload the tables, then both features will report every single row as a change!

     

    I think you really need to find a way to incrementally update your database tables and avoid the drop and recreate.  If you can simply add a row to your tables each day then both Change Tracking and CDC will be able to identify change efficiently for you and your occasionally connected application will be able to sync incremental changes.  If the external source can't provide you with only the new data, maybe you can query the database before updating or inserting data?

     

    Hope that helps!

     

    Regards, Mark

     

    Tuesday, September 16, 2008 6:07 PM
    Moderator
  • One more thing I can think of is:

    1. After you get the new data (the whole table's data), can you bulk load into a temp table

    2. Then do a diff between the current table and this temp table (the difference is 1 row as you say). Use the SQL Servers tablediff utility: http://msdn.microsoft.com/en-us/library/ms162843.aspx

    3. Use the generated SQL from step2 to do the incremental insert

     

    This way you dont need to drop and recreate your table (and hence not lose the change tracking metadata)

     

    Monday, September 29, 2008 7:35 PM