I currently have a single DB with data from a number of clients. I have been tasked with writing a script that splits the data up into separate DBs per client (each DB will have the same schema).
For the purpose of example, let's say the DB is filled with Artists, Songs, Videos, Reviews, and Locations. I've created separate DBs for each Client and I have a table that states which Artist belongs to which Client.
ArtistClient
- FK to Artist
- ClientDbName
Location
- ...
Artist
- FK to Location
- ...
Song
- FK to Artist
- ...
Video
- FK to Artist
- ...
Review
- FK to Song
- ...
In reality, there are many more relations than this, but this illustrates the issue.
The problem I'm having is following all of the FK relations amongst the tables. Is there any elegant way to iterate through all rows in Artist, get the ClientDbName from the ArtistClient table, and then insert all data related to the Artist (ascertained from analyzing FKs) into the proper DB?
