Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Importing data into a database with a complex schema using Java

 
M Burke
Ranch Hand
Posts: 406
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

Any suggestions?
 
Scott Selikoff
author
Saloon Keeper
Posts: 4028
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Mathias Nilsson
Ranch Hand
Posts: 367
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Remove the constraints before insert and add them again

-- disable the myconstraint constraint in the mytable table
ALTER TABLE mytable NOCHECK CONSTRAINT myconstraint


-- enable the myconstraint constraint in the table
ALTER TABLE mytable CHECK CONSTRAINT myconstraint
 
Stan James
(instanceof Sidekick)
Ranch Hand
Posts: 8791
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
M Burke
Ranch Hand
Posts: 406
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic