Not Null Issue from Datacompare with 2 source databases

rwatts50

Hi

Im new to this forum and also new to the redgate API and Im trying to develop a simple continuous integration application.

I have three databases, lets call them SourceDB1, SourceDB2 and Target.

Target and SourceDB2 will have the exact same schema while SourceDB1 will be missing some columns

I want to run a data compare so where the columns exist in SourceDB1 I use that data, and where columns don't exist I get the data from SourceDB2.

I can't just run two seperate datacompares as if any of the columns which don't exist in SourceDB1 are not null columns the code will fail.

Any pointers on this would be most grateful as Im a bit stucky :-)

Thanks

Brian Donahue

Hello,

SQL Data Compare Engine does have something that I think will help - the MissingFromXAsInclude option. If the column exists in db2, the option is MissingFrom2AsInclude and if the column exists in db1, the option is called MissingFrom1AsInclude.

Normally, SQL Data Compare had to have a matching schema to be able to script updates to the data, or appropriate mappings set up to divert data from one column to a different column in the same table. If there is a column that does not exist, this option will script updates for the column even if it is missing from one table schema or the other.

In this example, a table_1 exists in db1 and db2, but the data2 column only exists in db2. This code will script updates for the missing column.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using RedGate.SQLCompare.Engine;
using RedGate.SQLDataCompare.Engine;
using RedGate.Shared.SQL.ExecutionBlock;

namespace _64398
{
    class Program
    {
        static void Main(string[] args)
        {
            using (Database db1 = new Database())
            using (Database db2=new Database())
            {
                MappingOptions mapOptions = MappingOptions.Default | MappingOptions.MissingFrom2AsInclude;
                EngineDataCompareOptions engineOptions = new EngineDataCompareOptions(mapOptions, ComparisonOptions.Default, SqlOptions.Default);
                db1.RegisterForDataCompare(new ConnectionProperties("ps-briand\\sql2008r2", "64398B"));
                db2.RegisterForDataCompare(new ConnectionProperties("ps-briand\\sql2008r2", "64398A"));
                ComparisonSession dataSession = new ComparisonSession();
                dataSession.Options=engineOptions;
                TableMappings mappings = new TableMappings();
                mappings.Options = engineOptions;
                mappings.CreateMappings(db1.Tables, db2.Tables);
                dataSession.CompareDatabases(db1, db1, mappings);
                SqlProvider provider = new SqlProvider();
                provider.Options = engineOptions;
                ExecutionBlock block = provider.GetMigrationSQL(dataSession, true);
                string sqlScript = block.GetString();
                Console.WriteLine(sqlScript);
            }
        }
    }
}

The output may look something like this:

-- Update 987 rows in [dbo].[Table_1]
UPDATE [dbo].[Table_1] SET [data2]=N'E5 ' WHERE [id]=1
UPDATE [dbo].[Table_1] SET [data2]=N' ' WHERE [id]=2
UPDATE [dbo].[Table_1] SET [data2]=N'BSG80R10I ' WHERE [id]=3
UPDATE [dbo].[Table_1] SET [data2]=N'AP8Q32 ' WHERE [id]=4
UPDATE [dbo].[Table_1] SET [data2]=N'BQWTNX3 ' WHERE [id]=5