Compare fails because of data

pbz

Here's a common scenario that I was hoping SQL Compare would be able to help, but so far no luck.

Let's say I have a table (A), let's call this version 1. After a while I add a new table (B) and make a referential integrity check between A and B. So now I have in version 2 a new new table (B) and a modified version of table A that has data that's linked with B.

The problem here is that I can't really make v1 look like v2 without having the data from table B in v1. Here's what should happen:

1) Create table B in v1
2) Add data to table B from v2 into v1
3) Modify A from v1 and set any referential integrity checks

So basically I would need a mix of SQL Compare and SQL Data Compare, but I can't figure out how to make the two work together to achieve this goal.

Thanks.

P.S. Have I mentioned that this is a major pain in the !#@!?

Michelle T

I'm not sure I quite understand the problem. Have you tried synchronizing with SQL Compare ignoring constraints, then synchronizing the data with Data Compare, then doing a second pass with SQL Compare synchronizing the constraints?

pbz

Michelle Taylor wrote:

I'm not sure I quite understand the problem. Have you tried synchronizing with SQL Compare ignoring constraints, then synchronizing the data with Data Compare, then doing a second pass with SQL Compare synchronizing the constraints?

No, that's what I would have to do in order to get it to work. Right now, correct me if I'm wrong, since SQL Compare and SQL Data Compare are separate tools, I would have to run SQL Compare first to make v1 like v2. Doing this would create the new table B, but it will fail setting the referential integrity constraints because the data in table B is not there, therefore, the schema synchronization would fail. The data and schema go hand in hand, you can't separate the two in this scenario. So what's the solution for cases like this.

pbz

Michelle Taylor wrote:

Have you tried synchronizing with SQL Compare ignoring constraints, then synchronizing the data with Data Compare, then doing a second pass with SQL Compare synchronizing the constraints?

Maybe I didn't read that right, can you do that, and if so how?

Michelle T

First load up SQL Compare. Put in the information about your databases, then open up the project options and select 'Foreign Keys' and possibly 'Check Constraints' under 'Ignore'. (You can also ignore indexes, but you might want those to be created so you can use them as comparison keys in Data Compare). For more about options, see http://www.red-gate.com/supportcenter/Content.aspx?p=SQL%20Compare&c=SQL_Compare/help/7.1/sc_projectoptions.htm&toc=SQL_Compare/help/7.1/toc52647.htm. Synchronize your tables - as there are no constraints there ought to be no problems here.

Then load up SQL Data Compare, and synchronize the data that you need into the tables.

Finally, load up SQL Compare again, open the project you were using earlier or input your database information again, and switch off the Ignore Foreign Keys and Ignore Constraints (and possibly Ignore Indexes if you switched that on) options. Synchronize your tables - as the data is correct there should be no problem putting the constraints on.