loading into views

pamshields · #1 (**permalink**) 03-04-03, 13:05

I'm mapping data from multiple exisitng databases into an Oracle instance. I've setup a view that "looks like" the old data and have created an INSTEAD OF trigger to correctly insert the cols into the appropriate Oracle tables.

The question is - what's the best method for importing the data? SQL*Loader appears to only work with Tables and not Views. I'm considering UTL_FILE, but if anyone else has a better suggestion, please let me know!

jf_dba · #2 (**permalink**) 03-05-03, 07:51

Hi,

if you are using 9i you can use a EXTERNAL TABLE instead of the sqlloader. The you have to select from the EXTERNAL TABLE and insert into the VIEW.

In 8i you can load the data into a staging table with the sqlloader and the do an insert into ... select ....

You can load one flatfile-record into multible database-tables with the sqlloader. Maby this is an option to load the data without using the VIEW and instead-of-trigger. Using the sqlloader in this way is match faster then using instead-of-triggers.

Regards
JF

pamshields · #3 (**permalink**) 03-05-03, 08:20

Hey - thanks!! You've given me some good ideas!

Quote:

Originally posted by jf_dba
Hi,

if you are using 9i you can use a EXTERNAL TABLE instead of the sqlloader. The you have to select from the EXTERNAL TABLE and insert into the VIEW.

In 8i you can load the data into a staging table with the sqlloader and the do an insert into ... select ....

You can load one flatfile-record into multible database-tables with the sqlloader. Maby this is an option to load the data without using the VIEW and instead-of-trigger. Using the sqlloader in this way is match faster then using instead-of-triggers.

Regards
JF

Internet Services

Web Development

Digital Marketing

Consumer Tech