5

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.

Yaroslav Shabalin
  • 1,645
  • 3
  • 17
  • 29
jasim
  • 268
  • 1
  • 5
  • 16
  • 2
    The table needs to be empty and the data needs to be appended to the data in the table and you don't want to truncate it? This seems to be the opposite. Can you please clarify your question and post your SQL*Loader ctl file and the command line used. – Ben Jan 22 '14 at 09:52
  • I assume the 'table needs to be empty' is referring to the default behaviour in `INSERT` mode; the the rest is referring to what you want to happen? It would be helpful to show exactly what your command is doing (including the control file) and the error you get... – Alex Poole Jan 22 '14 at 10:33
  • @ben my log file error is SQL*Loader-601: For INSERT option, table must be empty. Error on table EMP – jasim Jan 22 '14 at 11:22
  • @AlexPoole my control file is like load data infile 'D:\data.txt' into table emp fields terminated by "," optionally enclosed by '"' (id,name) – jasim Jan 22 '14 at 11:24
  • @jasim - OK, then what I said in my answer applies. Add `append` before `into table` in your control file. But you should edit the question to include all the information you've been asked for, don't add it as comments. – Alex Poole Jan 22 '14 at 11:25

1 Answers1

9

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.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318