Red Gate forums :: View topic - Not Null Issue from Datacompare with 2 source databases
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Comparison SDK 10
SQL Comparison SDK 10 forum

Not Null Issue from Datacompare with 2 source databases

Search in SQL Comparison SDK 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
rwatts50



Joined: 10 Sep 2012
Posts: 1

PostPosted: Mon Sep 10, 2012 12:39 pm    Post subject: Not Null Issue from Datacompare with 2 source databases Reply with quote

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 Smile

Thanks
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6674

PostPosted: Wed Sep 12, 2012 5:30 pm    Post subject: Reply with quote

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:
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:
Quote:

-- 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
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group