If you are using Oracle database, at some point you
might have to deal with uploading data to the tables from a text file.
1)
Basic
Upload Example Using SQL*Loader
First, create the employee table as shown below.
SQL> CREATE TABLE EMPLOYEE
(
ID INTEGER,
NAME VARCHAR2 (10),
DEPT VARCHAR2 (15),
SALARY INTEGER,
HIREDAT DATE
)
Next create the
control file that explains what needs to be upload and where.
LOAD DATAINFILE 'C:\Users\evosys\Desktop\loader\employee.txt'
INSERT
INTO TABLE "HR"."EMPLOYEE"
FIELDS TERMINATED BY ','
(ID,
NAME,
DEPT,
SALARY,
HIREDAT)
Note: If you have the values inside the data file enclosed with double quote, use this in your control file: fields terminated by “,” optionally enclosed by ‘”‘
Note: If you don’t have the table created, you’ll get the following error message:
SQL*Loader-941: Error during describe of table EMPLOYEE
ORA-04043: object EMPLOYEE does not exist
You can pass the userid and password to the
sqlldr command using any one of the following format. As you see below, both of
these will prompt you for control file location, as it was not given in the
command line.$ sqlldr hr/hr
(or)
$ sqlldr userid=hr/hr
control =
SQL*Loader-287: No control file name specified.
Execute the sqlldr command to upload this new
record to the empty table by specifying both uid/pwd and the control file
location as shown below.$ sqlldr hr/hr
Control= C:\Users\evosys\Desktop\loader\emp_load.ctl
Commit point reached - logical record count 5
Verify the the records are created in the
databaseSQL> SELECT * FROM EMPLOYEE;
ID NAME DEPT SALARY HIREDAT
---------- ---------- --------------- ---------- -------
100 Thomas Sales 5000
200 Jason Technology 5500
300 Mayla Technology 7000
400 Nisha Marketing 9500
500 Randy Technology 6000
2. Inserting Additional Records
Let us say you want to add two new employees to the employee table from the following newemployee.txt file.600,Ritu,Accounting,5400
700,Jessica,Marketing,7800
If you create a similar control file like the
previous example, you might get the following error message.$ sqlldr hr/hr
Control= C:\Users\Desktop\loader\new_emp_load.ctl
SQL*Loader-601: For INSERT option, table must be empty.
Error on table EMPLOYEE
The above indicates that the table should be
empty before you can upload data using sql*loader.If you like to insert more data to the tables without having to delete the existing rows, use the “append’ command as shown in the following control file.
LOAD DATA
INFILE 'C:\Users\evosys\Desktop\loader\newemployee.txt'
APPEND
INTO TABLE "HR"."EMPLOYEE"
FIELDS TERMINATED BY ','
(ID,
NAME,
DEPT,
SALARY,
HIREDAT)
Now, if you do sqlldr this will append the data.
$ sqlldr hr/hr
Control= C:\Users\Desktop\loader\new_emp_load.ctl
Commit point reached - logical record count 2
Verify that the records are appended
successfullySQL> SELECT * FROM EMPLOYEE;
ID NAME DEPT SALARY HIREDAT
---------- ---------- --------------- ---------- ------
100 Thomas Sales 5000
200 Jason Technology 5500
300 Mayla Technology 7000
400 Nisha Marketing 9500
500 Randy Technology 6000
600 Ritu Accounting 5400
700 Jessica Marketing 7800
3. Data inside the Control File using BEGINDATA
You can also specify the data directly inside the control file itself using BEGINDATA keyword. i.e Anything that comes after BEGINDATA will be treated as data to be uploaded to the table as shown below.LOAD DATA
INFILE *
INTO TABLE
"HR"."EMPLOYEE"FIELDS TERMINATED BY ','
(ID,
NAME,
DEPT,
SALARY,
HIREDAT)
BEGINDATA
100,Thomas,Sales,5000
200,Jason,Technology,5500
300,Mayla,Technology,7000
400,Nisha,Marketing,9500
500,Randy,Technology,6000
Note: The infile will say ‘*’ in this case,
as there is no input data file name for this example.Execute sqlldr to upload the data from the control file.
$ sqlldr hr/hr
Control=C:\Users\Desktop\loader\emp_with_data.ctl
4. Date format and Different Delimiter
This example shows how to specify a date format in the control file and how to handle different delimiters in a data fileThe following example has different delimiters ($ after name, ^ after department).
100,Thomas$Sales^5000,31-JAN-2008
200,Jason$Technology^5500,01-Feb-2005
300,Mayla$Technology^7000,10-Aug-2000
400,Nisha$Marketing^9500,12-Dec-2011
500,Randy$Technology^6000,01-JAN-2007
Create the following control file and
indicate the field delimiters for each and every field using “terminated by” as
shown below.LOAD DATA
INFILE 'C:\Users\evosys\Desktop\loader\employee_format.txt' INSERT
INTO TABLE "HR"."EMPLOYEE"
FIELDS TERMINATED BY ','
(ID,
NAME terminated by "$",
DEPT terminated by "^",
SALARY,
HIREDAT DATE "dd-mon-yyyy")
Load the data using sqlldr as shown below.
$ sqlldr hr/hr
Control=C:\Users\Desktop\loader\emp_with_formate.ctl
Verify that the data got loaded properly as
shown below.SQL> SELECT * FROM EMPLOYEE;
ID NAME DEPT SALARY HIREDAT
--------- ---------- --------------- ---------- ------
100 Thomas Sales 5000 31-JAN-08
200 Jason Technology 5500 01-FEB-05
300 Mayla Technology 7000 10-AUG-00
400 Nisha Marketing 9500 12-DEC-11
500 Randy Technology 6000 01-JAN-07
5)
Change the data during upload
You can also massage the data and
change it during upload based on certain rules.
In the following control file:
- id is incremented by 999 before uploading. i.e if the emp id is 100 in the data file, it will be loaded as 1099
- Convert the name to upper case and load it. This uses the upper function.
- If the department contains the value “Technology” change it to “Techies”. This uses decode function
LOAD
DATA
INFILE
'C:\Users\evosys\Desktop\loader\employee.txt'
INSERT
INTO
TABLE "HR"."EMPLOYEE"
FIELDS
TERMINATED BY ','
( ID ":ID+999",
NAME "UPPER(:NAME)",
DEPT
"DECODE(:DEPT,'Technology','Techies', :DEPT)",
SALARY
)
Load the data using this control
file which will massage the data before uploading it.
$ sqlldr hr/hr
Control=C:\Users\Desktop\loader\emp_with_change.ctl
Verify that the data got changed
while loading as per our rules.
SQL>
SELECT * FROM EMPLOYEE;
ID
NAME DEPT SALARY HIREDAT
----------
---------- --------------- ---------- ------
1099
THOMAS Sales 5000
1199
JASON Techies 5500
1299
MAYLA Techies 7000
1399
NISHA Marketing 9500
1499
RANDY Techies 6000
6) Load data from multiple files
To load data from multiple files, you just have to specify multiple infile in the control file.The following control file loads data from two different data files (employee.txt and newemployee.txt) to the employee table.
LOAD DATA
INFILE 'C:\Users\evosys\Desktop\loader\employee.txt'
INFILE 'C:\Users\evosys\Desktop\loader\newemployee.txt'
INSERT
INTO TABLE "HR"."EMPLOYEE"
FIELDS TERMINATED BY ','
(ID,NAME,DEPT,SALARY,HIREDat)
7) Load data to Multiple Tables
Create another table called bonus which will have employee id and bonus columns.CREATE TABLE BONUS (ID INTEGER, BONUS INTEGER);
Create the employee-bonus.txt data file that contains the fields: id, name, department, salary, bonus
100 Thomas Sales 5000 1000
200 Jason Technology 5500 2000
300 Mayla Technology 7000 2000
400 Nisha Marketing 9500 1000
500 Randy Technology 6000 3000
Create the control file as shown below, which will upload the data from the above
file to two different tables. As shown below, you should have two “into table”
commands, and specify the position of the data which needs to be used to upload
the data to that column.
LOAD DATAINFILE 'C:\Users\evosys\Desktop\loader\emp_bouns.txt'
INTO TABLE "HR"."EMPLOYEE"
FIELDS TERMINATED BY ','
(ID,
NAME,
DEPT,
SALARY,
HIREDAT)
INTO TABLE "HR"."BONUS”
(ID,
BONUS)
Load the data to multiple tables using this control file as shown below.
$ sqlldr hr/hr
Control= C:\Users\Desktop\loader\mul_tab.ctl
Verify that the data got loaded to multiple tables
successfully.
SQL> select * from employee;
ID NAME DEPT SALARY HIREDAT
---------- ---------- --------------- ---------- ------
100 Thomas Sales 5000
200 Jason Technology 5500
300 Mayla Technology 7000
400 Nisha Marketing 9500
500 Randy Technology 6000
SQL> select * from bonus;
ID BONUS
---------- ----------
100 1000
200 2000
300 2000
400 1000
500 3000
No comments:
Post a Comment