Use SQL * Loader high-speed Batch Data Loading Tool

1. The control file contains the data to be loaded. First, create a test table SQLgt; showuserUSER is INGSQLgt; createtabledept2

1. The control file contains the data to be loaded. first create a test table SQLgt; show user USER is ING SQLgt; create table dept 2

1. The control file contains the data to be loaded.

First, create a test table.

Create a control file (including the loaded data)

Then execute the load command in the command line.

View dept table

View the demo1.log Log File

2. Separate control files from data files

Continue to use the previous dept table. First, create a control file and a data file.

Then execute the load command in the command line.

Finally, view the dept table.

3. Brief description of SQL * loader

Note: The number of braces on the left side of the code below is not part of the control file, just to facilitate display.

(1) LOAD DATA
(2) INFILE *
(3) INTO TABLE DEPT
(4) fields terminated ','
(5) (DEPTNO, DNAME, LOC)
(6) BEGINDATA
(7) 10, Sales, Virginia
(8) 20, Accounting, Virginia
(9) 30, Consulting, Virginia
(10) 40, Finance, Virginia
(1): This will tell sqlldr what to do. The above example indicates loading data.
(2): * indicates to load all the data (Example 1). You can also specify the name of the data file (Example 2 ).
(3): This will tell the table to which sqlldr is to be loaded. Complete Syntax: [insert | append | replace | truncate] into table dept insert-the default value is insert. The following dept table must be empty; append-append, the following dept table can not be blank; replace-is the first to delete and then insert; truncate-is the first to truncate and then insert.
(4): Tell sqlldr to separate values with commas.
(5) indicates that sqlldr data is to be loaded into the corresponding column.
(6): It indicates that sqlldr is about to load data.
(7 )~ (10): the specific data to be loaded.

4. FAQs

How to load special characters (quotation marks)

Query Result

Description: department 10 in Virginia, USA: this is because the data is "Virginia, USA ". The input data field must be included in brackets to retain the comma in the data.

VS, "USA": this is because the input data is "VS," "USA """. For strings caused by parentheses, the two occurrences are recorded as one occurrence.

How to load fixed-format data

View

Note: The above control file (deptno position (), dname position (), loc position () can also be changed to (deptno position (), dname position (*: 12), loc position (*: 20) has the same effect. The former is the absolute position, and the latter is the relative position.