Data Compare using an index when no index is present

ben_leahben_leah Posts: 12 Bronze 2
edited January 5, 2007 11:46AM in SQL Toolkit Previous Versions
OK, I have two tables that do not have any indexes on them, when i create the matching columns i do the following
TableMapping tableMapping = (TableMapping)mappings.Join(oldDatabase.Tables["[dbo].[W_CO_Measure_Definition]"], newDatabase.Tables["[dbo].[W_CO_Measure_Definition]"]);

                tableMapping.MatchingMappings.Clear();
                tableMapping.MatchingMappings.Add(tableMapping.FieldMappings["MeasureID"]);
                tableMapping.MatchingMappings.Add(tableMapping.FieldMappings["ApplicationCode"]);
                               
                tableMapping.RefreshMappingStatus();

This works fine, but any Update SQL is always keyed on all of the fields in the table (non unique index). How can i specify that the Update SQL should just be keyed on the field mappings (MeasureId, ApplicationCode)??

Comments

  • ben_leahben_leah Posts: 12 Bronze 2
    For example what i get is ..
    UPDATE [dbo].[w_co_measure_definition]
    SET    [presentationsql] = 'A DIFFERENCE '
    WHERE  [measureid] = 2104
           AND [applicationcode] = 'Client'
           AND [measurename] = 'ABL Ineligible Total (13)'
           AND [defaultdisplayname] = 'ABL Ineligible Total (13)'
           AND [fielddescription] = 'ABL Ineligible Total (13)'
           AND [sqlalias] = 'a'
           AND [sqlfield] = 'ABLIneligibleTotal13Amt'
           AND [alarmfield] = ''
           AND [columnformat] = '(#,#0)'
           AND [alarmcode] = ''
           AND [columnwidth] = 10
           AND [datatype] = 'Numeric'
           AND [summarytype] = 'Both'
           AND [presentationsql] = 'COALESCE(AVG(a.ABLIneligibleTotal13Amt), 0) / AVG(x.ExchangeRate)'
           AND [portfolioselection] = ''
           AND [selectionorder] = 0
           AND [showmemoalerts] = 'N'
           AND [displayableinapplication] = 'Y'
           AND [maturityindicator] = ''
    

    What I want is :
    
    UPDATE [dbo].[w_co_measure_definition]
    SET    [presentationsql] = 'A DIFFERENCE '
    WHERE  [measureid] = 2104
           AND [applicationcode] = 'Client'
    
    
    
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello Ben,

    Your code certainly looks like it would give you the result you are looking for. The SQL code that's output looks like it is using a comparison key that combines 19 columns.

    Is it possible that the session object you are using as an argument in the GetMigrationSql method is using a diferent session than the one you are applying these mappings to?
Sign In or Register to comment.