Re: Need help importing data.

Nick Harvey wrote:

> Exporting and importing data. Is there any way to export just the data
> from an oracle table, and re-import it into a table with a different
> structure?
>
> Any help would be appreciated.

Dead easy. Forget the ORACLE export/import facility use SQLPLUS to export as follows :

set newpage 0
set space 0
set linesize MAX_LENGTH_OF_RECORD
set pagesize 0
set echo off
set feedback off
set heading off
set termout off
spool export.txt
select field1||'|'||field2 from export_table; spool off

MAX_LENGTH_OF_RECORD should be set to whatever the appropriate number is. The order in which the fields are selected will govern there order in the output file (export.txt). The ||'|'|| bit concatenates the fields together and provides a delimiter or |.

Next stage is to create the import table eg.

CREATE import_table (field1 char(10), field2 char(10));

Then build a SQL*LOADER script similar to this

LOAD DATA
INFILE 'export.txt'
INTO TABLE import_table
(field1 CHAR TERMINATED BY '|',
 field2 CHAR TERMINATED BY WHITESPACE);

and run it thru SQL*LOADER swop either the export or import order of the fields to shift them around. Beware the use of TERMINATED BY WHITESPACE if field2 has embedded spaces because you'll only get up to the first space, if this is a problem add an extra pipe of the end of the SELECT (ie field1||'|'||field2||'|') and put TERMINATED BY '|' instead.

By the way is both tables are in the same database you can just do

CREATE TABLE import_table AS SELECT field2,field1 FROM export_table;

and forget all the import export crap.

HTH Jason Received on Fri May 29 1998 - 10:22:05 CDT