Oracle Bulk Import

sql oracle dbvisualizer
Jai picture Jai · Mar 7, 2014 · Viewed 8.5k times · Source

Bulk Import in Oracle

I have created the table emp and I need to import bulk data from a file into emp

For Ex 

CREATE TABLE emp

( c1 NUMBER,

  c2 VARCHAR2(30)

)

File path : 'C:\Documents and Settings\TestUser\My Documents\LearnOracle\reports.csv'

Answer

Maheswaran Ravisankar picture Maheswaran Ravisankar · Mar 7, 2014

Write a Control file, say emp.ctl

load data
 options(direct=true)
insert
 into table emp
fields terminated by ',' optionally enclosed by '"' TRAILING NULLCOLS
 ( 
    c1 DECIMAL EXTERNAL,
    c2
)

Execute SQL*Loader as below.

sqlldr userid=user/pass@db data=reports.csv control=emp.ctl

If you always want to refresh the table with your data completely. TRUNCATE the table and then load.

SQL*Loader is great utility that provide numerous options for performance. Kindly go through the documentation about DIRECT and CONVENTIONAL loading.

There's something called EXTERNAL tables, that might interest you as well. They use the flat file for the table data.

Related questions

dbvisualizer: set max rows in a select query

I am using DBVisualizer 8.0.6 and when I run a simply query like.... select * from table It only shows the first 1000 rows and then stops the query and displays in the bottom left corner... "Number of rows limited by maxrows" How …

Read More
Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More