|SQL*LOADER|
A log file has created after completing the process. You can see the log file to ensure all the records are uploaded successfully or not. You will see 1 record is rejected which is the header of the CSV data.
Now see the data from EMP_TEST_DATA table.
SQL Loader is a powerful tool to upload bulk external data to
oracle database. It is oracle provided tool. Its performance is higher than the
traditional upload data using Toad or any other tools. The SQL loader can be
used to read data from external data. We can load data from multiple data files
during the same load session using SQL Loader. There is an example that how you
can upload data using SQL Loader.
Step-01:
Assume that HR default
schema is available. Sample SQL to generate a CSV(Comma Separated Value) file:
select
employee_id,first_name,last_name, hire_date,Salary
from employees
order by 1;
Save SQL output as a CSV file |
Export the data as CSV and save the file as EMP_DATA.csv
Create a test
table as EMP_TEST_DATA:
create table emp_test_data
as
select
employee_id,first_name,last_name, to_char(hire_date,'DD-Mon-YYYY')
hire_date,Salary
from employees
where 1=2;
Step-02:
Write a control file as below and save the file with EMP_DATA.ctl
LOAD DATA
INFILE 'EMP_DATA.csv'
BADFILE 'EMP_DATA'
DISCARDFILE 'EMP_DATA'
INSERT INTO TABLE emp_test_data
APPEND FIELDS TERMINATED BY
"," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(employee_id,first_name,last_name,
hire_date,Salary)
Step-03:
Create a batch file using below code and save the code as Load_Data.bat
@echo off
sqlldr userid = hr/hr@orcl control = emp_data.ctl log = emp_data.log
pause
(Note: change the UserID or Connection string as you have available)
Now create a folder and put the above three files (EMP_DATA.csv, EMP_DATA.ctl & Load_Data.bat)
in that folder. Now double click on Load_data.bat
file. It will start to insert data into EMP_TEST_DATA table. See the below
picture:
SQL Loader CMD Console
A log file has created after completing the process. You can see the log file to ensure all the records are uploaded successfully or not. You will see 1 record is rejected which is the header of the CSV data.
Log file of SQL*Loader |
Now see the data from EMP_TEST_DATA table.
Final Output |