I am making a utility to read a database and generate SQL INSERT statements for each row in every table. I want to be able to move data with a script from one DB to a DB at another job site. I am currently using MS SQL 2000.
Problem is...the schema has foreign key (FK) constraints. I get the meta data from the database and get a list of the tables. But it gives me the list in alphabetical order. It has no concept of FK constraints. So when I go to run the script, I get FK violations because some rows holding the FK have not yet been created.
One solution might be to set all foreign key constraints to null (or some record that represents null if the column is set as NOT NULL) then after the data has been inserted, go back and use update commands to set all fields properly.
Which database are you using? Many provide internal methods for dumping entire databases into SQL statements.
Another solution is read all of the data from the database and load it into Hashtables. Then once all Hashtable references have been resolved, insert the data such that if you encounter a reference that has not been inserted, insert it before its parent.
There's a number of other techniques that will work although trying to do this dynamically for an entire database can be difficult, *especially* if records cross reference each other between two different tables.
You may be able to drill further into metadata and actually get all the constraints so you can follow the dependencies and insert the tables in the required order. I did a school project that build a model of the database by parsing DDL just so I could handle these dependencies.
Disabling and enabling the constraints is far simpler but runs a risk of failing when you put them back on due to invalid values.
A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
Joined: Jun 25, 2004
Thanks for the help guys. Turning off the constrants seems to be the easy way out. Since the data is valid in one db, it should...hopefully...be valid in an identical schema elsewhere.
subject: Importing data into a database with a complex schema using Java