Re: select/insert into/from a file ?

From: Walter T Rejuney <BlueSax_at_Unforgetable.com>
Date: Sat, 02 Sep 2000 11:20:19 -0400
Message-ID: <39B11AB3.7805E292@Unforgetable.com>

Lisa Spielman wrote:

> I need to move data from 1 database to another and need
> to do this using files. There are about 8 tables and the
> largest table has about 100 rows.
>
> Is there a simple way to do this in Oracle ?
>
> Informix makes this really easy:
> unload to x.dat select * from tableX
> load from x.dat ....
>
> Can I select * from table x into a file, and then insert
> from this file into the same table in another database ?
>
> thanks, Lisa

The oracle way of doing this is with export. Using that method you can either export an entire database, an entire user, or just a single table. Along with tables you will get any constraints, grants and indexes.

However, if you have already set up the environment on the target database and merely want to move data and nothing else, you can dump the data to a flat file with a script like this:

set pages 0;
set feedback off;
set termout off;
spool mydata.dat
whenever sqlerror exit failure;

select col1||'|'||
          col2||'|'||
          col3||'|'||

from mytable;
exit success;

You can then use sqlloader to bring the data into your new table - although sqlloader itself requires a control file that you will have to set up first.

Another alternative is to write your own loader routine. Depending on what platform you are working with this could be relatively easy or relatively difficult. For instance, if you have a UNIX platform you could take the output file from the sql query above and spin through it with something similar to this:

IFS='|'
exec 3<mydata.dat
while read -u3 col1 col2 col3 junk;do

    Stmt='INSERT INTO MYTABLE (COL1,COL2,COL3) values ('${col1}','${col2}','${col3}');
sqlplus -s login/pass <<sqlEOF
set pages 0;
set feedback off;
whenever sqlerror exit failure;
${Stmt};
exit success;
sqlEOF

    if [[ $? -ne 0 ]];then

        print -u2 "Fatal error from sqlplus while inserting values ${col1} ${col2} ${col3}";

        exit 1;
    fi
    done

If you are on a windows platform then you'd probably have to write something in VB or VC++, but if you go to that trouble then you'd want to learn either OCI or Pro-C. Learning sqlloader is far easier. Received on Sat Sep 02 2000 - 10:20:19 CDT