Update tables with a unreliable foreign key

Compares and synchronizes SQL database content.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, Michelle Taylor, chengvoon.tong

Update tables with a unreliable foreign key

Postby tadhg » Thu Nov 21, 2013 4:18 pm

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
Code: Select all
ID Name
1   Donkey
2   Elephant


Table2
Code: Select all
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
Code: Select all
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?
tadhg
 
Posts: 2
Joined: Thu Nov 21, 2013 3:35 pm

Postby andy.campbell.smith » Thu Nov 21, 2013 8:50 pm

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?
Andy Campbell Smith

Red Gate Technical Support Engineer
andy.campbell.smith
 
Posts: 173
Joined: Thu Oct 20, 2011 11:19 am
Location: Red Gate Software

Postby tadhg » Fri Nov 22, 2013 5:00 pm

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.
tadhg
 
Posts: 2
Joined: Thu Nov 21, 2013 3:35 pm

Postby andy.campbell.smith » Fri Nov 22, 2013 5:45 pm

OK - let me know how you get on!
Andy Campbell Smith

Red Gate Technical Support Engineer
andy.campbell.smith
 
Posts: 173
Joined: Thu Oct 20, 2011 11:19 am
Location: Red Gate Software


Return to SQL Data Compare 10

Who is online

Users browsing this forum: No registered users and 1 guest