Does Data Compare recognize foreign keys in selected rows?

Compares and synchronizes SQL database content.

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

Does Data Compare recognize foreign keys in selected rows?

Postby sillimans » Tue Jul 22, 2014 6:10 pm

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.
sillimans
 
Posts: 6
Joined: Tue Jul 22, 2014 5:04 pm

Postby Evan Moss » Tue Jul 22, 2014 10:11 pm

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/displ ... s+or+views)

Hope that helps.

Thanks,
Evan
Evan Moss
Product Support
(866) 627-8107
Evan Moss
 
Posts: 99
Joined: Wed Jun 12, 2013 9:56 pm

Re:

Postby sillimans » Wed Jul 23, 2014 3:30 am

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?
sillimans
 
Posts: 6
Joined: Tue Jul 22, 2014 5:04 pm

Postby Evan Moss » Wed Jul 23, 2014 5:12 pm

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
Evan Moss
 
Posts: 99
Joined: Wed Jun 12, 2013 9:56 pm

Re:

Postby sillimans » Thu Jul 24, 2014 2:56 pm

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.
sillimans
 
Posts: 6
Joined: Tue Jul 22, 2014 5:04 pm

Postby Evan Moss » Thu Jul 24, 2014 8:20 pm

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
Evan Moss
 
Posts: 99
Joined: Wed Jun 12, 2013 9:56 pm


Return to SQL Data Compare 10

Who is online

Users browsing this forum: sana and 0 guests