Table vs View Comparison

jijobaby

I am trying to compare Table and view but looks like i am getting no where.

I tried doing it by creating the object of Tablemapping , but then i found out that we cannot mapping it to a view using this object sicne the property called "Status" is giving unsuccessful. Example

tableMapping = (TableMapping)mappings.Join(db1.Tables["[dbo].[" + srcTable + "]"], db2.Views["[dbo].[" + destTable + "]"]);

SO,
i got another object called ViewsAndTables but looks like this one is also throwing some casting error. We are not sure if we can use this object
viewTableMapping = (ViewsAndTables)mappings.Join(db1.Tables["[dbo].[" + srcTable + "]"], db2.Views["[dbo].[" + destTable + "]"]);

Error thrown here is

Error 1 Cannot convert type 'RedGate.SQLCompare.Engine.Mapping<RedGate.SQLCompare.Engine.ViewTableSuperClass>' to 'RedGate.SQLCompare.Engine.ViewsAndTables'


Can anyone help me here?

jijobaby

I am using SQL Data Compare for the Table-View Mapping. However if you look at the screenshot you will be able to see that the Status is shown as "unsuccessful". Generally when i do the mapping between the tables then the status is shown as "success"

What can be the reason for the same . Let me know your thoughts

Brian Donahue

Hi,

You are probably trying to join two tables that don't have any matching columns and/or primary keys. In that case you have to construct the mappings manually by adding FieldMappings and MatchingMappings. Please see: http://www.red-gate.com/support/Knowled ... apping.pdf .

jijobaby

Thanks so much for your mail. After having such informatic conversation i feel that i am closer to the solution.


Here is what i am doing here to construct the mappings manually by adding FieldMappings and MatchingMappings.

tableMapping.MatchingMappings.Clear();
tableMapping.MatchingMappings.Add(tableMapping.FieldMappings["DownloadID"]);
tableMapping.MatchingMappings.Add(tableMapping.FieldMappings["CultureGroupId"]);
tableMapping.RefreshMappingStatus();

session.CompareDatabases(db1, db2, mappings);
TableDifference difference = session.TableDifferences["[dbo].[" + srcTable + "]"];

Here when i try to do a quick watch on the Mappings object i can see that the Status is shown as PartialSuccess. I understand i need to have the status as Success for the difference to come correctly.
Currently its showing some difference which doesnt make any sence.

Is there something that is to be done to get my status as Success?

NOTE : When i do this on tables i get Status = Success

I really appreciate your help here. Thanks a ton

Brian Donahue

MatchingMappings are the comparison key mappings. Don't forget to add all of the other FieldMappings for columns that you aren't using to match rows.