Data Synchronization - write only new rows on target

mpetcov · October 13, 2015 7:15PM

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

Allen LeVan · October 14, 2015 4:29PM

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.

mpetcov · October 14, 2015 5:05PM

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&#91;table.SchemaQualifiedName&#93;;
                    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?

Mike U · October 16, 2015 6:01AM

TableDifference has a SqlSynchronization property that can be set to one or more of the following values:

&#91;Flags&#93;
public enum SqlSynchronization
{
    /// &lt;summary&gt;No update SQL will be produced.&lt;/summary&gt;
    None = 0x00,

    /// &lt;summary&gt;Include the update SQL.&lt;/summary&gt;
    UpdateSql = 0x01,

    /// &lt;summary&gt;Include the delete SQL.&lt;/summary&gt;
    DeleteSql = 0x02,

    /// &lt;summary&gt;Include the insertion SQL.&lt;/summary&gt;
    AddSql = 0x04,

    /// &lt;summary&gt;Include all SQL ( default ).&lt;/summary&gt;
    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;
}

Data Synchronization - write only new rows on target

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk