Update tables with a unreliable foreign key

tadhg

Sorry if this has been asked but I'm struggling to find the answer.

Using SQL Data Compare I wish to update a table but I cannot guarantee the same value foreign key without looking at the foreign table. Say for example I have two tables:

Table1

ID Name
1   Donkey
2   Elephant

Table2

ID FK_ID Name
1   1        Carrots
2   2        Peanuts

Where FK_ID is pointing at Table1

The SQL script I'm generating from DataCompare looks something like

INSERT INTO Table2 (FK_ID,Name) VALUES (1,'Carrots') ... etc

What I'm concerned about is if my target database has Table1 has values as such:

Table1

ID Name
1   Elephant
2   Donkey

Then Carrots will be linked to Elephant instead of Donkey (although I'm sure the Elephant would not mind :-P).

Is there a way to tell Data Compare to look at the 'Name' column of Table1 to ensure that the correct FK_ID is maintained?

Andy C S

Hi Tadhg,

There's not really a way to do exactly this in SQL Data Compare - I guess we'd recommend that you first update Table1 from the source database so that you can be sure to maintain referential integrity?

Alternatively, you could create a view containing all the important fields in both databases and compare that? Do either of those options sound practicable in your environment?

tadhg

The columns are already named and embedded across mutliple environments so updating tables is a big task.

I like the View suggestion. I will investigate that idea.

Andy C S

OK - let me know how you get on!