Load data from csv file to oracle table from command line

Question

I tried with sql loader.The thing is the table needs to be empty for data loading.Is there any way to do data upload without truncating the table.The CSV data need to be appended in the table.i am using oracle 11g.

Answer 1

The SQL*Loader documentation says:

When you are loading a table, you can use the INTO TABLE clause to specify a table-specific loading method (INSERT, APPEND, REPLACE, or TRUNCATE) that applies only to that table. That method overrides the global table-loading method. The global table-loading method is INSERT, by default, unless a different method was specified before any INTO TABLE clauses.

So by default your table load will be in INSERT mode, which does require the table to be empty.

The documentation also explains how to load data into a non-empty table; in your case you want to preserve the existing data:

APPEND
If data already exists in the table, then SQL*Loader appends the new rows to it. If data does not already exist, then the new rows are simply loaded. You must have SELECT privilege to use the APPEND option.

So your control file will need to say something like this (as shown in their example):

LOAD DATA
INFILE 'my_file.dat'
BADFILE 'my_file.bad'
DISCARDFILE 'my_file.dsc'
APPEND
INTO TABLE my_table
...

You could also consider using the new CSV data as an external table and inserting to your real table from that, which might be a bit more flexible.