Oracle uses SQL Loader to load data

I. in Oracle databases, we usually use the following methods to copy or migrate records between tables in different databases:

1. The records in Table A are exported to insert statements separated by semicolons and then inserted into Table B.

2. create A dblink between databases, and then use create table B as select * from A @ dblink where..., or insert into B select * from A @ dblink where ....

3. exp Table A, and imp to table B. query conditions can be added for exp.

4. The program implements select from A... and then insert into B.... Batch submission is also required.

5. SQL Loader (sqlldr) is used to import data, which is much more effective than insert.

2. Execute the sqlldr command of Oracle on the command line. You can see its detailed parameter description:

Username -- Oracle Database Name.

Password -- Oracle Database Password.

Servicename -- the name of the Oracle service instance.

Control: Control file, which may contain table data.

Log-records the log file during import. The default value is control file (remove Extension). Log.

Bad-bad data file. The default value is control file (remove Extension). Bad.

Data-Data file, which is generally specified in the control file. It is more suitable for automatic operations if no data file is specified in the parameter control file.

Errors -- number of allowed error records. You can use it to control the number of error records.

Rows-the number of records submitted once. The default value is 64.

Skip: the number of skipped lines. For example, the first few lines of the exported data file are headers or other descriptions.

Insert -- the default method. The table is required to be empty when data loading starts.

Append -- Append a new record to the table

Replace -- delete the old record (using the delete from table statement) and Replace it with the newly loaded record

Truncate -- delete the old record (with the truncate table statement) and replace it with the newly loaded record

OPTIONS (skip = 1, rows = 128) -- The OPTIONS displayed by the sqlldr command can be written here. skip = 1 is used to skip the first row of data and submit it every 128 rows.

Fields terminated by "," -- the field interval is ","

Optionally enclosed by '"'-row record Interval

Trailing nullcols -- the table field can be empty if it does not have a corresponding value

Iii. Use of sqlldr:

1. Use only one control file, which contains data.

2. Use a control file (as a template) and a data file.

The second approach is generally used to facilitate the separation of templates and data, and different labor unions of programs.

Iv. Example:

The second method is used here.

Create a table file_list:

Create table file_list

(

File_id number,

File_name varchar2 (250 ),

Created_date date

);

Create a data file file_list.data

15968600 ^ 025_value_added_0_201011301014_030937.Tdat ^ 2010-11-30 10:18:20

15968572 ^ 025_call_0_201011301012_030907.Tdat ^ 10:16:16

15968596 ^ 025_data_0_201011301015_030964.Tdat ^ 10:17:53

Create the control file file_list.ctl

OPTIONS (ROWS = 1000)

Load data

Infile 'e: \ file_list.dat'

BADFILE 'e: \ log \ error. bad'

Replace into table file_list

Fields terminated by '^'

Trailing nullcols

(

FILE_ID

, FILE_NAME

, CREATED_DATE "YYYY-MM-DD HH24: MI: SS"

)

V. Run:

Click Start> run and type cmd.

Run the following command:

E: \> sqlldr username/password @ servicename control = file_list.ctl