Data Synchronization - write only new rows on target

mpetcovmpetcov Posts: 5 Bronze 2
edited October 16, 2015 6:01AM in SQL Comparison SDK 11
I am attempting to sync data with the following options at table & column level (please assume that I am fully aware of the t-sql constrains on this):
1. Initial sync: assumes that the target is empty and writes all data;
2. Always: always sync the data from source to target;
3. New Rows Only: insert in target only the new rows from source(by primary key);

The code below should handle my inclusions (please correct me if I am wrong):
TableMappings mappings = new TableMappings();
mappings.Options = new EngineDataCompareOptions(MappingOptions.Default, ComparisonOptions.Default, SqlOptions.Default);
mappings.CreateMappings(sourceScripts.Tables, targetDb.Tables);
                
foreach (TableMapping mapping in mappings)
{
         mapping.Include = true/false;
         foreach (var fieldMapping in mapping.FieldMappings)
         {
                 filedMapping.Include = true/false;
         }
}

Now, for tables that should sync only new rows, how do I exclude the other differences?
I was expecting something in TableDifferences:
TableDifferences diffs = session.TableDifferences;
foreach (TableDifference diff in diffs)
{
       //will it work in here? how?
}

Thanks,
Mihai

Comments

  • Hello,

    Thanks for the question on the SQL Comparison SDK. For this you would have to look at the differences of the table comparison results and determine for the differences if the row existed only in source (a new row) or in both source and target (an update to an old row). Then only synch the rows which are new which would be differences that only exist in source. I don't believe we have a method that does this automatically but you could build the logic into your foreach loop.
    Allen LeVan
    Red Gate Software
    US Product Support
  • mpetcovmpetcov Posts: 5 Bronze 2
    Hi Allen,

    I'm afraid you'll need to be a bit more specific.
    Here is what I got so far:
    ComparisonSession session = new ComparisonSession();
                    session.Options = mappings.Options;
    
                    foreach (var table in tablesForWhichWeDeployOnlyNewRows)
                    {
                        var tableDiffs = session.TableDifferences[table.SchemaQualifiedName];
                        if (tableDiffs != null)
                        {
                            foreach (var row in tableDiffs.ResultsStore)
                            {
                                 if(row.Type != Row.RowType.In1)
                                {
                                    //how do I Include/exclude this row?
                                }
                            }
                        }
                    }
    

    Am I on the right track? How do I exclude those differences/rows?
  • TableDifference has a SqlSynchronization property that can be set to one or more of the following values:
    [Flags]
    public enum SqlSynchronization
    {
        /// <summary>No update SQL will be produced.</summary>
        None = 0x00,
    
        /// <summary>Include the update SQL.</summary>
        UpdateSql = 0x01,
    
        /// <summary>Include the delete SQL.</summary>
        DeleteSql = 0x02,
    
        /// <summary>Include the insertion SQL.</summary>
        AddSql = 0x04,
    
        /// <summary>Include all SQL ( default ).</summary>
        AllSql = AddSql | UpdateSql | DeleteSql
    }
    

    So inside your loop in your initial forum post, you'd set SqlSynchronization to AddSql:
    foreach (TableDifference diff in diffs)
    {
        diff.SqlSynchronization = SqlSynchronization.AddSql;
    }
    
    Development Lead
    Redgate Software
Sign In or Register to comment.