Red Gate forums :: View topic - Does Data Compare recognize foreign keys in selected rows?
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

Does Data Compare recognize foreign keys in selected rows?

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



Joined: 22 Jul 2014
Posts: 4

PostPosted: Tue Jul 22, 2014 6:10 pm    Post subject: Does Data Compare recognize foreign keys in selected rows? Reply with quote

I source controlled a database to my local server, so what I have are empty tables. I need a small data set for testing, and I don't want to wait for data compare to fill the empty table with the original 800,000 + rows. I know I can choose specific rows in data compare, but if I compare 2 tables, is there a way for data compare to automatically select rows from Table 2 based on the foreign key in Table 1?

I'm just trying to find a way to avoid selecting each row for each table individually, and was wondering if there is an automated recognition on Data Compare's side.
Back to top
View user's profile Send private message
Evan Moss



Joined: 12 Jun 2013
Posts: 99

PostPosted: Tue Jul 22, 2014 10:11 pm    Post subject: Reply with quote

Hello,

Thanks for your post.

I can't think of a very useful way to deploy only a subset of the data in your tables while also keeping the foreign key constraints intact.

SQL Data Generator might be a better tool for your use case. You can generate test data and also have cross-column and cross-table dependencies. It should automatically recognize your foreign keys as well.

You can also use your original table as a data source and just pick some proportion of the rows. (See this documentation article: http://documentation.red-gate.com/display/SDG3/Mapping+SQL+tables+or+views)

Hope that helps.

Thanks,
Evan
_________________
Evan Moss
Product Support
(866) 627-8107
Back to top
View user's profile Send private message Send e-mail
sillimans



Joined: 22 Jul 2014
Posts: 4

PostPosted: Wed Jul 23, 2014 3:30 am    Post subject: Re: Reply with quote

Thanks for the reply. In my brief experience with Data Generator, I noticed that the identity columns were re seeded. I have some tables with identity columns that start in the thousands. I would prefer to retain those identity values to keep it as close to production as possible - is this possible?
Back to top
View user's profile Send private message
Evan Moss



Joined: 12 Jun 2013
Posts: 99

PostPosted: Wed Jul 23, 2014 5:12 pm    Post subject: Reply with quote

Hello,

I just did a quick test to see how it handled an identity column starting at 1000, and it appears to work as expected.

Thanks,
Evan
_________________
Evan Moss
Product Support
(866) 627-8107
Back to top
View user's profile Send private message Send e-mail
sillimans



Joined: 22 Jul 2014
Posts: 4

PostPosted: Thu Jul 24, 2014 2:56 pm    Post subject: Re: Reply with quote

My identity columns are server assigned, and I can't see the value until after the insert, and after reseeding. Does DG have an option to not treat identities as a server assigned value?

Also, my identity column starts at 1, but because rows were deleted, the min identity value is ~20k. I'm not sure if in your testing you started identity at 1,000 or your min identity value was 1,000.
Back to top
View user's profile Send private message
Evan Moss



Joined: 12 Jun 2013
Posts: 99

PostPosted: Thu Jul 24, 2014 8:20 pm    Post subject: Reply with quote

Hello,

There's no way that I know of to turn off the server assignment, but I just looked into this more and it will only try to reseed if you have the "delete data from table before generation" option checked. As long as you're not deleting data, if the next server assigned value is 20000, SQL Data Generator will just go from there.

Hope that helps.

Thanks,
Evan
_________________
Evan Moss
Product Support
(866) 627-8107
Back to top
View user's profile Send private message Send e-mail
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