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


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.
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
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google