Existing Data Source and Foreign Keys

MckMurrayMckMurray Posts: 37 Bronze 4
edited July 14, 2016 8:27AM in SQL Data Generator
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?
· Share on Twitter

Comments

  • Eddie DEddie D Posts: 1,807 Rose Gold 5
    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
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter
  • MckMurrayMckMurray Posts: 37 Bronze 4
    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
    · Share on Twitter
  • KatieSchmidtKatieSchmidt Posts: 2 New member
    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?
    · Share on Twitter
  • KatieSchmidtKatieSchmidt Posts: 2 New member
    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.
    · Share on Twitter
Sign In or Register to comment.