Does Data Compare recognize foreign keys in selected rows?

sillimans · July 22, 2014 1:10PM

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.

Evan Moss · July 22, 2014 5:11PM

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

sillimans · July 22, 2014 10:30PM

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?

Evan Moss · July 23, 2014 12:12PM

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

sillimans · July 24, 2014 9:56AM

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.

Evan Moss · July 24, 2014 3:20PM

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

Does Data Compare recognize foreign keys in selected rows?

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk