I have a quite large ER model in my database and lots of data. Now I would like to take a subset of this data into a different database for testing purposes. The problem is how to get a subset from all tables so that I don't break referential integrity and get orphanded records from the database? I mean that if I choose 100 records e.g. From the "product" table, how can I easily get all the data from the referenced tables (and from the tables these tables are referenced)? Is there any tool availble for this purpose or just pure SQL?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
Unfortunately there isn't a cookie-cutter way to do this. You have to start with some base records, write queries that join against them and then repeat. As you go deeper the queries get more complex as they have to go right back to the root.
If you have the foreign keys then you can automate the generation of the queries, as the joins will just be through the foreign key columns. It's not as hard as it sounds, but here are a few pointers if you take this approach:
Automation will probably get you 95% of the way with some manual intervention and the scripts aren't all that hard to write. It's not a 5 minute job, but it's certainly possible.
You could programatically generate SSIS packages to do the load, but I think that generating bcp control files is probably easier. Another option would be to just structure the scripts so they copy the data from the queries into another shadow database. This can then be backed-up/restored to wherever you want.
It's not a trivial undertaking but it's certainly not beyond the wit of man. As a bonus, if you can configure your script so the starting keys can be parameterised, then you can make a generalised utility to copy subsets of your application database. This will be quite useful for rolling out test environments.
The strategy would depend on the design of the schema.
You could take the first 1000 rows from each table and go from there. If you have constraints/keys/etc. that you cannot break then you need to identify the data to use. One way to do this is to create a list of key values from one of the tables and then select out from the other tables using a filter like:
or you could just use a subquery to replace the
Another alternative is the make a copy of your database then delete a bunch of data (is there an associated application to the db that has a suitable delete function?) It may just be easier to recreate some data - especially if the database has an application attached to it.