Red Gate forums :: View topic - Update tables with a unreliable foreign key
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Data Compare 10
SQL Data Compare 10 forum

Update tables with a unreliable foreign key

Search in SQL Data Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
tadhg



Joined: 21 Nov 2013
Posts: 2

PostPosted: Thu Nov 21, 2013 4:18 pm    Post subject: Update tables with a unreliable foreign key Reply with quote

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


Table2
Code:
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:
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 Razz).

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?
Back to top
View user's profile Send private message
andy.campbell.smith



Joined: 20 Oct 2011
Posts: 159
Location: Red Gate Software

PostPosted: Thu Nov 21, 2013 8:50 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
tadhg



Joined: 21 Nov 2013
Posts: 2

PostPosted: Fri Nov 22, 2013 5:00 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
andy.campbell.smith



Joined: 20 Oct 2011
Posts: 159
Location: Red Gate Software

PostPosted: Fri Nov 22, 2013 5:45 pm    Post subject: Reply with quote

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

Red Gate Technical Support Engineer
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic 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