SQLDataCompare Execute order for Tables

aperregatturv

Hi Brian,

I have 2 databases, db1 and db2 I am trying to copy data from db1 to db2 and both database schemas are identical. My problem is
I want to copy data in certain order and ignore certain tables, is this possible?

Reason being I have to populate certain master tables first before copying data to other tables or child tables.
This is what i did.
****
Database db1 = new Database();
Database db2 = new Database();

db1.RegisterForDataCompare(new ConnectionProperties("SQL2000","LiveDB01","**",""));
db2.RegisterForDataCompare(new ConnectionProperties("SQL2000", "ArchiveDB01", **"", ""));
TableMappings mappings = new TableMappings();

mappings.CreateMappings(db1.Tables, db2.Tables);

ComparisonSession session = new ComparisonSession();
session.CompareDatabases(db1, db2, mappings);
TableDifferences t = session.TableDifferences;
//i check the count of tables here
foreach (TableDifference td in t)
{
if ((td.Name.ToLower() == "[dbo].[event]") )
{
td.RefreshSelected = false; // This doesnt work..
}
}
session.RefreshSelectedItems();
// and here the count of tables here they are same it should one less, right?
block = provider.GetMigrationSQL(session, true);
BlockExecutor executor = new BlockExecutor();
executor.ExecuteBlock(block, "SQL2000", "ArchiveDB01", false, "**", "");
****
I get an error on one of the tables, Primary key error. I know the problem.
I need to insert data into table1 first before inserting into table2 or table3 but i am not sure how to set which table should be executed first.

Please let me know
Thanks
Arun

Brian Donahue

Hi Arun,

You may choose to ignore tables by going through the collection of TableMappings and setting the 'Include' property on the mapping object to 'false'. I believe there are instructions for this elsewhere in the forums.

You cannot force the order of the table updates, but it should alleviate the second problem if you use the 'disable primary keys' and/or 'disable foreign keys' option in the options. This would disable (or drop) these keys during synchronization, and enable (or create) them after the synchronization is done.

ccollins

When you include the tables involved in a relationship, the TSQL code to disable the fkey is created.

If you do not include the other related tables, or you are generating the migrationsql one table at a time, the TSQL code to disable the fkey is not created.

I also noticed that the DisableTrigger is involved with this as well.

Scenario Detail:
[no disable or drop] - EntityContent and Version table require record inserts. EntityContent must have related records in Version first. Update one table at a time, EntityContent comes first. No disable trigger or drop constraint code is created. The update fails

[disable and drop] - EntityContent and Version table require record inserts. EntityContent must have related records in Version first. Update all tables, EntityContent comes first. Disable trigger and drop constraint code is created. The update succeeds.


The solution is to have an external file, (i.e. metadata), that defines the dependency order. This could be a simple sequence integer value that orders your table update process. Alternatively, you could query the dependencies for the tables, building a dynamic update sequence. Use either method to update your tables in the required order.