Copying a subset of data to an empty database with the same schema

Question

I would like to export part of a database full of data to an empty database. Both databases has the same schema. I want to maintain referential integrity.

To simplify my cases it is like this:

MainTable has the following fields:
1) MainID integer PK
2) Description varchar(50)
3) ForeignKey integer FK to MainID of SecondaryTable

SecondaryTable has the following fields:
4) MainID integer PK (referenced by (3))
5) AnotherDescription varchar(50)

The goal I'm trying to accomplish is "export all records from MainTable using a WHERE condition", for example all records where MainID < 100.

To do it manually I shuold first export all data from SecondaryTable contained in this select:

select * from SecondaryTable ST
outer join PrimaryTable PT
 on ST.MainID=PT.MainID

then export the needed records from MainTable:

select * from MainTable
where MainID < 100.

This is manual, ok. Of course my case is much much much omre complex, I have 200+ tables, so donig it manually is painful/impossible, I have many cascading FKs.

Is there a way to force the copy of main table only "enforcing referntial integrity".

so that my query is something like:

select * from MainTable
where MainID < 100
WITH "COPYING ALL FK sources"

In this cases also the field (5) will be copied.

======================================================

Is there a syntax or a tool (also a not free one, but it must do what I need, not only a subset of the features like "db compare with no options") to do this?

Table per table I'd like to insert conditions (like MainID <100 is only for MainTable, but I have also other tables).

Answer 1

One approach would be to simply do a backup and restore of the database. Then, you can easily delete the rows with a script like this:

delete from SecondaryTable1 where MainID < 100
delete from SecondaryTable2 where MainID < 100
...

Assuming you have cascade delete turned on, that seems the simplest to me. Otherwise you would need something like this:

delete from Table1 where SecondaryTable1MainID < 100
delete from SecondaryTable1 where MainID < 100
delete from Table2 where SecondaryTable2MainID < 100
delete from SecondaryTable2 where MainID < 100
...

You can automate the creation of the scripts like this:

select 'delete from ' + TABLE_NAME + ' where MainID < 100' 
from information_schema.tables

Answer 2

That sounds pretty much like what Jailer tool does, http://jailer.sourceforge.net/