Syncing table and related data

• 
  Thanks for your post and apologies for the delay in getting back to you.
  
  If the table has dependency can it not use the same clause? Do these table have Foreign key relationship?

  Anu D January 13, 2015 11:27

  - 0 +
• 
  Is there any update on this?
  If table A has a foreign key relationship to table B, is there a way to automatically include those records?
  (Data compare will fail at the end of the deployment, quoting the foreign key relationship as the reason for failure.)

  bkim October 17, 2016 14:48

  - 0 +
• 
  I am also interested to know if this is possible. The WHERE clause is great, but I have a hierarchy of related tables with dependent data. Not all of them relate back to the top level key, only the second level. Third and fourth level tables relate one level up.
  
  It would be cool if the Deployment Options had a toggle to exclude orphaned data or include only related data so all those rows without a parent don't get moved.

  ramseyjacob February 08, 2017 11:20

  - 0 +
• 
  Has there been any progress/updates on this front? I'm attempting to automate data compares and avoid the FK failures without leading to untrusted FK relationships.

  Stevie June 08, 2020 21:55

  - 0 +
• 

  ids said:

  I am trialing SQL Data Compare to solve the issue of copying / syncing data from a production database to a training environment.
  
  I don't want to copy all the data but instead a selected snapshot - the last weeks data.
  
  The main table I want to copy has a date on it so I can easily filter the rows I want from that using a WHERE clause. However linked to the main table is a number of other tables that I can't use the same restriction on.
  
  Is there anyway to have the data synchronization copy a row from a table and then all dependent rows from related tables?

  It sounds like you're on the right track with SQL Data Compare for your data synchronization needs. To achieve your goal of copying a specific snapshot of data along with its dependent rows, consider these steps:

  1. Identify Dependencies: Start by mapping out the relationships between your main table and the dependent tables. This will help you understand which rows you need to copy.

  2. Use SQL Scripts: Instead of relying solely on SQL Data Compare, you might create a custom SQL script that:

     • Copies the rows from your main table that match your date filter using a SELECT statement with a WHERE clause.
     • For each copied row, retrieves and copies the dependent rows from the linked tables. You can use JOIN statements to get these rows based on foreign key relationships.

  3. Transaction Management: Ensure that your operations are wrapped in a transaction to maintain data integrity. This way, if any part of the process fails, you can roll back all changes.

  4. Testing: Before executing in the production environment, thoroughly test your script in a development or test environment to ensure it works as expected.

  By combining SQL Data Compare with custom SQL scripting, you should be able to selectively copy your desired data along with its dependencies.

  halla01 October 05, 2024 11:34

  - 0 +

Add comment

Please sign in to leave a comment.