Not Null Issue from Datacompare with 2 source databases

Automate and integrate using the SQL comparison API

Moderators: Chris Auckland, David Atkinson, Michelle Taylor, chengvoon.tong

Not Null Issue from Datacompare with 2 source databases

Postby rwatts50 » Mon Sep 10, 2012 12:39 pm

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
rwatts50
 
Posts: 1
Joined: Mon Sep 10, 2012 12:24 pm

Postby Brian Donahue » Wed Sep 12, 2012 5:30 pm

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.
Code: Select all
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
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Comparison SDK 10

Who is online

Users browsing this forum: No registered users and 0 guests