Copying a subset of data to an empty database with the same schema
- by user193655
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 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).
