Data Synchronization - write only new rows on target

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

mpetcov October 13, 2015 19:15

Comments

3 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 October 14, 2015 16:29

0

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?

mpetcov October 14, 2015 17:05

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;
}

Mike U October 16, 2015 06:01

Add comment

Please sign in to leave a comment.

How can we help you today?

Data Synchronization - write only new rows on target

Comments

Add comment