| Author |
Message |
rwatts50
Joined: 10 Sep 2012
Posts: 1
|
 Posted: Mon Sep 10, 2012 12:39 pm Post subject: Not Null Issue from Datacompare with 2 source databases |
 |
|
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 |
|
| Back to top |
|
 |
Brian Donahue
Joined: 23 Aug 2004
Posts: 6345
Location: Red Gate Software
|
| Posted: Wed Sep 12, 2012 5:30 pm Post subject: |
|
|
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
|
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521 |
|
| Back to top |
|
|
|
|
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
