Oracle Data synchronization: Oracle SQL Loader usage Instructions (high-volume fast Insert database records)

1. Prepare table Data

Select *  fromEmp10;Createsequence Seq_eseqincrement by 1Start with 1001MaxValue999999999;--get a sequence of SQL statementsSelectSeq_eseq.nextval fromsys.dual;--Delete a sequence of SQLDROPSEQUENCE seq_eseq;Create TableEmp10 (empno Number(4)Primary Key, enamevarchar2( -) not NULL, Jobvarchar( -) not NULL, Mgr Number(4), HireDate date,sal Number(5,2), Comm Number(5,2), Deptno Number(2),constraintDept_emp10_fkForeign Key(DEPTNO)ReferencesDept (DEPTNO))Insert  intoemp10 (Empno,ename,job,mgr,hiredate,sal,comm,deptno)Values(Seq_eseq.nextval,'Lili','IT',980, To_date ('1988-05-11','YYYY-MM-DD'), -, -,Ten);Select Count(*) fromEMP10;

Export CSV file

After export, the double quotation marks in the CSV file are removed.

2.ctl File Parameter Description:

LOAD DATA
INFILE emps.csv--csv file name
Append--operation type, append new record to table with append
into table Emp10--Insert record table name
Fields TERMINATED by ","--comma delimited
(
Virtual_column FILLER,--Skip the CSV file generated by PL/SQL Developer, first column ordinal
EMPNO "Seq_eseq.nextval"--this column takes the next value of the sequence directly, instead of the value provided in the data
Ename Upper (: ename) ",--, process data with SQL functions or operations
JOB,
MGR,
HireDate Date ' Yyyy-mm-dd ',--Specifies the format of the accepted date, equivalent to the to_date () function conversion
SAL,
COMM,
DEPTNO
)

Description: In the action type append location, one of the following values is available:

1) Insert--The default way to require the table to be empty at the beginning of the data load
2) Append--Append a new record to the table
3) Replace-deletes the old record (with the DELETE from table statement) and replaces the newly loaded record
4) Truncate--delete old record (with TRUNCATE TABLE statement), replace new loaded record

3. Execute the command parameter description:
Eg:sqlldr Scott/[email protected] Control=emps.ctl log=emps.log skip=1

UserID-Oracle's username/password[@servicename]
Control-controls files that may contain table data
-------------------------------------------------------------------------------------------------------
Log-logs file when importing, default to control file (remove extension). log
Bad--The default is the control file (remove extension).
Data--file, usually specified in the control file. Not specifying data files in parameter control files is more appropriate for automatic operation
Errors--The number of error records allowed, which can be used to control a record.
Rows-How many records are submitted once, default to 64
Skip-The number of skipped rows, such as the first few rows of the exported data file is the header or other description

Oracle_sqlloader test. rar

Oracle Data synchronization: Oracle SQL Loader usage Instructions (high-volume fast Insert database records)