Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Loading Data...
A copy of this was sent to "steve" <sjm26_at_hotmail.com>
(if that email address didn't require changing)
On Tue, 16 Mar 1999 20:41:33 +1100, you wrote:
>I need to load a few of million rows into an empty database (the biggest
>tables will require between 1 and 2 million each). The rows are currently
>sitting in flat files....
>
>What is generally the best strategy for approaching this task. I have used a
>program called BCP under Sybase that seemed to work pretty well - Is there
>an Oracle equivalent? Is it any good?
>
the program is sqlldr. would recommend
direct path mode is very similar to bcp (but faster...) no triggers or constraints will be fired
>If I consider a row-by-row approach using a c program is it likely to finish
>within the millenium? I will be dropping all indexes and triggers before
>starting.
>
>I appreciate your comments and experience.
>
>steve.
>
>(Also; Is there an Oracle equivalent of the...
>
>UPDATE A SET A.X = B.X FROM B WHERE A.KEY = B.KEY
>
>statement I used to use in Sybase???)
>
there are 2 general syntaxes for that:
SQL> create table t1 ( a int primary key, b varchar2(25) ); Table created.
SQL> create table t2 ( x int , y varchar2(25) ); Table created.
SQL> insert into t1 values ( 1, 'table 1 data' ); SQL> insert into t1 values ( 2, 'table 1 data' );
SQL> insert into t2 values ( 1, 'table 2 data' ); SQL> insert into t2 values ( 2, 'table 2 data' ); SQL> insert into t2 values ( 3, 'table 2 data' ); SQL> commit;
SQL> select * from t2;
X Y
---------- ------------------------- 1 table 2 data 2 table 2 data 3 table 2 data
SQL> update ( select * from t1, t2 where t2.x = t1.a )
2 set y = b
3 /
2 rows updated.
SQL> select * from t2;
X Y
---------- ------------------------- 1 table 1 data 2 table 1 data 3 table 2 data
SQL> rollback;
Rollback complete.
SQL>
SQL> update t2
2 set y = ( select b from t1 where t1.a = t2.x )
3 where x in ( select a from t1 )
4 /
2 rows updated.
SQL> select * from t2;
X Y
---------- ------------------------- 1 table 1 data 2 table 1 data 3 table 2 data
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities