0

We have RDBMS that stores data in a normalized format .

1) What is the best format for import export from one DB to another assuming it might have binary data too ? Perhaps XML would be good ? XLS has 2 draw backs of each cell restricted to 32K and Binary Data not suited for XLS .

2) Are there any cool tools out there that understand foreign keys and handles them intelligently ? That is , it exports the foreign key row and maintains the relationship .

Ofcourse Primary IDN's are no longer relevant in new system - can be dropped from the base table . If there are tools that can preserve the Foreign Key row ( perhaps recursively if there are child primary idns and no circular reference ) that would be something that I want . Let me rephrase the quesion : How to import export partial tables maitaining their RELATION in Relational Databases like Oracle and SQL ? Further the format should be XML or something standard .A sort of Deep Copy of the data from tables would be an appropriate description of the task

How Do I Deep Copy a Set of Data, and Change FK References to Point to All the Copies? looks like a similar question .

Simple Table: 

Student_IDN    Name    City_IDN
1              Frank   5
2              Mark    7

City_IDN   City_Name City_Description City_Additional_Info
5          London    England          Country Visa....         
7          Newwark   USA              Europe Visa 
Cœur
  • 37,241
  • 25
  • 195
  • 267
Nishant
  • 20,354
  • 18
  • 69
  • 101
  • 4
    Which RDBMS vendor (MySQL, postgres, sql server) are you importing from and to? There are are specialized tools for each. – Alex Weinstein Nov 11 '13 at 15:14
  • Cross data-base solution . It is for the end user . For exmample you import Firefox bookmark and you can import it to any where . However I am also open to hearing solutions in Oracle and SQL . The main thing is this is for the end user and not DB admins . For example we configure something on UAT and the end user after making it perfect imports to Prod . – Nishant Nov 11 '13 at 15:17
  • "*Oracle and SQL*" doesn't really makes sense. Oracle's query language ***is*** SQL. Or did you want to *exclude* PL/SQL? –  Nov 11 '13 at 15:25
  • In this context probably Nishant means MS sql. – Lajos Veres Nov 11 '13 at 15:45
  • Yes MSSQL :-) Somehow SQL is synonymously used as MSSQL sometimes by your truly . – Nishant Nov 11 '13 at 16:32

1 Answers1

1

I think all rdbms supports consistent backups. Anyway you have to be aware that by default backups are optimised to be less intrusive rather than more consistent. But if you can disable connecting to your db before dumping then this is not a problem. Otherwise if you are looking for a "general" solution then something flexible needing more customisation can be your answer. (xml/csv/json for example)

Lajos Veres
  • 13,595
  • 7
  • 43
  • 56