Existing Data Source and Foreign Keys

MckMurray

As part of my test setup I need to import 8 or 10 "reference" tables, which have foreign key relationships between them.

How can I set it up so that it generates in a logical order such that the referenced tables are done first, then the referencing tables? I can't find any option for this, and it just gives me warnings that it may run into errors because of the FK relationships.

Is it possible?

Eddie D

Hi

Thank you for your forum post.

SQL Data Generator itself looks after the dependencies between objects and therefore generates the data in dependency order. The product will warn if you have not included objects for data generation that have a FK relationship to the objects that you are generating data for.

Many Thanks
Eddie

MckMurray

Thanks Eddie,
I see now that is mostly working - although there is one case that isn't:

One of the tables I want to import has an optional FK to another table that I do not want to import. In the source data there is only one row that has a value in this FK column. I chose the option to "skip the row" if invalid data is encountered, but it doesn't seem to be doing that. Instead, it fails the whole table insert (inserts 0 rows), and all subsequent tables can't be updated because it says "IDENTITY_INSERT is already on for table <failed table>. Cannot perform SET operation for table <subsequent table>".

Any suggestions?
Thanks

KatieSchmidt

MckMurray said:

Thanks Eddie,
I see now that is mostly working - although there is one case that isn't:

One of the tables I want to import has an optional FK to another table that I do not want to import. In the source data there is only one row that has a value in this FK column. I chose the option to "skip the row" if invalid data is encountered, but it doesn't seem to be doing that. Instead, it fails the whole table insert (inserts 0 rows), and all subsequent tables can't be updated because it says "IDENTITY_INSERT is already on for table <failed table>. Cannot perform SET operation for table <subsequent table>".

Any suggestions?
Thanks

Is there a resolution to this?  Do you have to turn off identity columns for all the tables getting populated in order for this to properly work?

KatieSchmidt

I figured this out.  the "Copy Identity column values"  was checked.I thought this meant to copy over the identity values from the source database and therefore set it to true.