I need to sync a product table and 2 linked tables with images and translations.
I've made 2 custom queries to compare the images and translations with the SKU coming from the product table. When I hit the synchronisation button, it only takes my "Product" table for the sync operation.
Is it possible to sync the custom query comparisons too?
Custom query synchronisation
-
alexa
Re: Custom query synchronisation
dbForge Data Compare for SQL Server doesn't allow to perform synchronization between the pair of the objects having specified a custom query as the Target. It is only allowed when a custom query is specified as the Source and a table or view is specified as the Target.
However, if you can suggest us the way we can generate INSERT/UPDATE/DELETE for a named custom query, we will review such functionality extension.
However, if you can suggest us the way we can generate INSERT/UPDATE/DELETE for a named custom query, we will review such functionality extension.
Re: Custom query synchronisation
Hello,
I have the same problem. And I have a suggestion:
Let pretend that we have 2 tables: Counties (Country_ID, Country_Name) and Cities (City_ID, Country_ID, City_Name, Mayor_Name)
and you need to sync Mayor_Name in table Cities in two different DBs. The IDs columns are identity columns and could have different values in different DBs for the same City_Name. The City_Name is not unique for the table Cities, however its unique for one Country. So you can't use IDs to compare, you have to use Names instead. Taking this into account we have such Query to compare:
SELECT Countries.Country_Name, Cities.City_Name, Cities.Mayor_Name
FROM Cities INNER JOIN Countries ON Cities.Country_ID = Counties.Country_ID
So I choose Country_Name and City_Name as keys, table Cities and field Mayor_Name to be updated, and as a result of Comparison Tool I would like to see something like this:
UPDATE Cities
SET Mayor_Name = 'New Mayor1'
FROM Cities INNER JOIN Countries ON Cities.Country_ID = Counties.Country_ID
WHERE Countries.Country_Name = 'USA' AND Cities.City_Name = 'Washington'
UPDATE Cities
SET Mayor_Name = 'New Mayor2'
FROM Cities INNER JOIN Countries ON Cities.Country_ID = Counties.Country_ID
WHERE Countries.Country_Name = 'USA' AND Cities.City_Name = 'New York'
etc.
I have the same problem. And I have a suggestion:
Let pretend that we have 2 tables: Counties (Country_ID, Country_Name) and Cities (City_ID, Country_ID, City_Name, Mayor_Name)
and you need to sync Mayor_Name in table Cities in two different DBs. The IDs columns are identity columns and could have different values in different DBs for the same City_Name. The City_Name is not unique for the table Cities, however its unique for one Country. So you can't use IDs to compare, you have to use Names instead. Taking this into account we have such Query to compare:
SELECT Countries.Country_Name, Cities.City_Name, Cities.Mayor_Name
FROM Cities INNER JOIN Countries ON Cities.Country_ID = Counties.Country_ID
So I choose Country_Name and City_Name as keys, table Cities and field Mayor_Name to be updated, and as a result of Comparison Tool I would like to see something like this:
UPDATE Cities
SET Mayor_Name = 'New Mayor1'
FROM Cities INNER JOIN Countries ON Cities.Country_ID = Counties.Country_ID
WHERE Countries.Country_Name = 'USA' AND Cities.City_Name = 'Washington'
UPDATE Cities
SET Mayor_Name = 'New Mayor2'
FROM Cities INNER JOIN Countries ON Cities.Country_ID = Counties.Country_ID
WHERE Countries.Country_Name = 'USA' AND Cities.City_Name = 'New York'
etc.
-
alexa
Re: Custom query synchronisation
This is a very particular case.
Though, we will investigate a possibility of implementing a logic to generate a sync script for a custom query when developing the next product versions.
Though, we will investigate a possibility of implementing a logic to generate a sync script for a custom query when developing the next product versions.
Re: Custom query synchronisation
thanksalexa wrote:This is a very particular case.
Though, we will investigate a possibility of implementing a logic to generate a sync script for a custom query when developing the next product versions.
I don't think that is too particular, most of dev teams works with several environments: DEV, TEST, STAGE, PROD, and you want them to be in sync sometimes, however, the PKs may not be the same across all environments.
you can create a temp view for update, or allow to choose views in Data Comparison tool. There are several restrictions for this, however, these restrictions can be easily addressed when you create your own temp view:
https://msdn.microsoft.com/en-us/library/ms180800.aspx