Syncing table and related data

ids

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?

Anu D

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?

bkim

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.)

ramseyjacob

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.

Stevie

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.

halla01

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.