Import data from a single file into multiple Oracle tables

Question

Is there a method to import data from a single file into multiple Oracle tables while maintaining the referential integrity?

score 2 · Accepted Answer · answered Nov 15 '11 at 20:21

2

Yes.

Without a lot more detail, I'll just say that you should look to external table to get the data from the file into the database, then select from the external table and use the 'INSERT ALL' feature to insert into multiple tables, from the single input.

Hope that helps.

answered Nov 15 '11 at 20:21

Mark J. Bobak

13,720
6
39
67

Hi Mark,Can you please explain the details? Can I do this with SQL Developer? – user602599 Nov 29 '11 at 19:32

score 1 · Answer 2 · answered Nov 15 '11 at 23:04

There are couple alternatives (not an exhaustive list):

Walk the dependency graph of FOREIGN KEYs and make sure you insert data to "parents" before inserting it to "children".
Defer all the FOREIGN KEYs, so order of insertion does not matter. This is OK if you can perform the whole import in a single transaction.
Temporarily disable FOREIGN KEY constraints, import the data in any order, then re-enable them.

Import data from a single file into multiple Oracle tables

2 Answers2