How to Append Extra New Records to Table Using Oracle SQL loader?

advertisements

_____________________________________________________________________________________________________________________

Suppose if you have some records already inserted in the database table and you wanted to add few more records to the same table, in this case you will need this option to append the record. Most of the time, you will be not in a position to truncate the table and reinsert the data to the table because the available volume of the data in the table might be huge or it might have used or referenced for some other purpose.
In such situations you can use below mentioned method to append the data.

 SQL*Loader Features and Benefits

EXAMPLE

I have a table called append_exam with two columns-Name and phonenumber. I have few records in the table and I wanted to add few more records to the using SQL Loader.

SQL> select * from append_exam;

NAME                      PHONE

-------------------- ----------

SUKESH               9898989899

STEVE                9898989900

JAMES                9898989901

I have a text file call new_records.txt with few more records which needs to be appended to theappend_exam table.

$vi new_records.txt

WARD,9898989902

JONES,9898989903

MARTIN,9898989904

For that you will have to create a control file for the SQL*Loader. In that control file if you specify the INSERT keyword it will throw below mentioned error as it is having some records already.

SQL*Loader-601: For INSERT option, table must be empty.  Error on table append_exam

In our example we need to append the data along with the data in the table. So you should modify your SQL*Loader control file in the following format. 

$ vi new_rec_append.ctl

load data

infile '/home/smt/data/new_records.txt'

append

into table append_exam

fields terminated by ","

( name, phone )

Now you can execute the following command in the OS prompt to append the new records into the table.

$ sqlldr scott/tiger control=/home/smt/data/new_rec_append.ctl

Commit point reached - logical record count 3

SQL> select * from append_exam;

NAME                      PHONE

-------------------- ----------

SUKESH               9898989899

STEVE                9898989900

JAMES                9898989901

WARD                 9898989902

JONES                9898989903

MARTIN               9898989904

_____________________________________________________________________________________________________________________

Website Stats