1
. Ordinary loading
Load Data
INFILE *
INTO TABLE DEPT
Replace
Fields Terminated by ',' Optionally Enclosed By '"
(DePTNO,
DNAME,
LOC
)
Begindata
10, Sales, "" "USA" ""
20, Accounting, "Virginia, USA"
30, Consulting, Virginia
40, FINANCE, VIRGINIA
50, "Finance", "", virginia // LOC column will be empty
60, "Finance", the virginia // LOC column will be empty
2
.
Specify that not loading the column
Load Data
INFILE *
INTO TABLE DEPT
Replace
Fields Terminated by ',' Optionally Enclosed By '"
(DePTNO,
Filler_1 filler, // The following "Something Not to Be Loaded" will not be loaded
DNAME,
LOC
)
Begindata
20, Something Not to Be loaded, Accounting, "Virginia, USA"
3
. use
Position
List
Load Data
INFILE *
INTO TABLE DEPT
Replace
(Deptno Position (1: 2),
DNAME POSITION (*: 16), // The beginning of this field at the end position of the previous field
LOC POSITION (*: 29),
Entire_Line Position (1:29)
)
Begindata
10accounting virginia, USA
4
. Use a function, an expression of the date,
Trailing nullcols
usage of
Load Data
INFILE *
INTO TABLE DEPT
Replace
Fields terminated by ','
Trailing nullcols // Actually, the following Entire_Line is not directly corresponding in the data behind Begindata.
If the value of the column is changed to 10, Sales, Virginia, 1/5/2000, no trailing nullcols
(DePTNO,
DNAME "Upper (: DNAME)", // use functions
LOC "Upper (: LOC)",
Last_updated Date 'DD / MM / YYYY', an expression of 'DD-MON-YYYY', etc.
Entire_Line ": deptno ||: DName ||: Loc ||: last_updated"
)
Begindata
10, Sales, Virginia, 1/5/2000
20, Accounting, Virginia, 21/6/1999
30, Consulting, Virginia, 5/1/2000
40, FINANCE, VIRGINIA, 15/3/2001
The functions here can also be custom functions
5
. Merger multi-line records are a line record
Load Data
INFILE *
Concatenate 3 // See a few lines of records through keyword constnate
INTO TABLE DEPT
Replace
Fields terminated by ',' (Deptno,
DNAME "Upper (: DNAME)",
LOC "Upper (: LOC)",
Last_updated Date 'DD / MM / YYYY'
)
Begindata
10, Sales, // In fact, this 3 lines see a row of 10, Sales, Virginia, 1/5/2000
Virginia,
1/5/2000
// This list uses continueif list = ","
Tell SqllDr to find a comma at the end of each line to attach the next line to the previous line
Load Data
INFILE *
Continueif this (1: 1) = '-' // Looking for the beginning of each row with connection characters - there is a line to connect the next line
// such as -10, Sales, Virginia,
// 1/5/2000 is a line 10, Sales, Virginia, 1/5/2000
//, 1: 1 means that from the first row and ends in the first line, there is Continueif next but Continueif List is ideal.
INTO TABLE DEPT
Replace
Fields terminated by ','
(DePTNO,
DNAME "Upper (: DNAME)",
LOC "Upper (: LOC)",
Last_updated Date 'DD / MM / YYYY'
)
Begindata / / But it seems to be used like the right side.
-10, Sales, Virginia, -10, Sales, Virginia,
1/5/2000 1/5/2000
-40, 40, Finance, Virginia, 13/04/2001
Finance, Virginia, 13/04/2001
6
. Line number loaded into each line
Load Data
INFILE *
INTO TABLE T
Replace
(seqno recnum // Loading each line)
Text Position (1: 1024))
Begindata
FSDFASJ / / Automatically assign a line number to the seqno field loaded in Table T This behavior 1
Fasdjfasdfl // This behavior 2 ...
Seduce
.
Load data with a changing
Note: Unix and Windows Difference // N & / N
<1> Using a non-chain character
Load Data
INFILE *
INTO TABLE DEPT
Replace
Fields terminated by ','
Trailing nullcols
(DePTNO,
DNAME "Upper (: DNAME)",
LOC "Upper (: LOC)",
Last_updated "MY_TO_DATE (: Last_updated)",
Comments "Replace (: Comments, '/ N', CHR (10))" // Replace usage helps to convert newline characters
)
Begindata
10, Sales, Virginia, 01-April-2001, this Is The Sales / Noffice In Virginia
20, Accounting, Virginia, 13/04/2001 This Is The Accounting / NOFFICE IN VIRGINIA
30, Consulting, Virginia, 14/04/2001 12: 02: 02 This is the consulting / NOFFICE IN VIRGINIA
40, Finance, Virginia, 987268297, this is the finance / noffice in virginia <2> Using FIX properties
Load Data
Infile demo17.dat "fix 101"
INTO TABLE DEPT
Replace
Fields terminated by ','
Trailing nullcols
(DePTNO,
DNAME "Upper (: DNAME)",
LOC "Upper (: LOC)",
Last_updated "MY_TO_DATE (: Last_updated)",
Comments
)
Demo17.dat
10, Sales, Virginia, 01-April-2001, this is the sale
Office in virginia
20, Accounting, Virginia, 13/04/2001 this is the accounting
Office in virginia
30, Consulting, Virginia, 14/04/2001 12: 02: 02 this is the consulting
Office in virginia
40, Finance, Virginia, 987268297, this is the finance
Office in virginia
// This load will not use the method under the database into the database, but the format of the data is different.
Load Data
Infile demo18.dat "fix 101"
INTO TABLE DEPT
Replace
Fields Terminated by ',' Optionally Enclosed By '"
Trailing nullcols
(DePTNO,
DNAME "Upper (: DNAME)",
LOC "Upper (: LOC)",
Last_updated "MY_TO_DATE (: Last_updated)",
Comments
)
Demo18.dat
10, Sales, Virginia, 01-April-2001, "this is the sale
Office in virginia
20, Accounting, Virginia, 13/04/2001, "this is the accounting
Office in virginia
30, Consulting, Virginia, 14/04/2001 12:02:02, "this is the consulting
Office in virginia
40, Finance, Virginia, 987268297, "this is the finance
Office in virginia
<3> Use a VAR attribute
Load Data
Infile demo19.dat "var 3"
// 3 Tell the first 3 bytes of each record indicate that the length of the record is like the 071 of the first record indicates that this record has 71 bytes.
INTO TABLE DEPT
Replace
Fields terminated by ','
Trailing nullcols
(DePTNO,
DNAME "Upper (: DNAME)",
LOC "Upper (: LOC)",
Last_updated "MY_TO_DATE (: Last_updated)",
Comments
)
Demo19.dat
07110, Sales, Virginia, 01-April-2001, this Is The SalesOffice In Virginia
07820, Accounting, Virginia, 13/04/2001, this is the accounting
Office in virginia
08730, CONSULTING, VIRGINIA, 14/04/2001 12: 02: 02, this is the consulting
Office in virginia
07140, Finance, Virginia, 987268297, THIS THE FINANCE
Office in virginia
<4> Using STR Attributes
// The most flexible one can define a new row of tailings WIN Enter backhand: chr (13) || CHR (10)
The records in this column are ended with A | / R / N
SELECT UTL_RAW.CAST_TO_RAW ('|' || CHR (13) || CHR (10)) from DUAL
Result 7c0d0a
Load Data
Infile demo20.dat "str x'7c0d0a '"
INTO TABLE DEPT
Replace
Fields terminated by ','
Trailing nullcols
(DePTNO,
DNAME "Upper (: DNAME)",
LOC "Upper (: LOC)",
Last_updated "MY_TO_DATE (: Last_updated)",
Comments
)
Demo20.dat
10, Sales, Virginia, 01-April-2001, this is the sale
Office in virginia |
20, Accounting, Virginia, 13/04/2001 this is the accounting
Office in virginia |
30, Consulting, Virginia, 14/04/2001 12: 02: 02 this is the consulting
Office in virginia |
40, Finance, Virginia, 987268297, this is the finance
Office in virginia |
8
.
Data like this
use
Nullif
Clause
10-JAN-200002350FLIPPER SEEMED UNUSUALLY TODAY.
10510-JAN-200009945SPRead over Three Meals.
ID Position (1: 3) nullif id = blanks / / This can be Blanks or other expressions
// The following is another List of 1 1 in the database will become NULL in the database.
Load Data
INFILE *
INTO TABLE T
Replace
(n Position (1: 2) Integer external nullif n = '1',
v position (3: 8)
)
Begindata
1 10
20LG
-------------------------------------------------- ------------
If it is an English log format, you may need to modify environment variables NLS_LANG or NLS_DATE_FORMAT
9
.
Import LOB
data
We all know that it is very annoying to handle LOB data. After the database design is completed, we often need to load test data, and we can use SQL * loader to implement, FOLLOWING IS The Control File for LOB.
Load DataInfile *
INTO TABLE BIG_TABLE
Replace
Fields terminated by x'09 'optionally enclosed by' "
Trailing nullcols
(
XX__ID INTEGER EXTERNAL,
XX_Name Char,
Video_File Filler Char,
Video Lobfile (Video_File) Terminated by EOF NULLIF VALUE_FILE = 'NULL'
)
Begindata
1 matrix3 martrix3.mpg
2 "Lord of Ring" Ring3.AVI
3 "TOP 10 of WEEK" TOP10_3.RA
Description:
Filler is the Loader reserved word, indicating that video_file is a variable rather than a field name, and the Filler Char is a string variable, which is used later.
Lobfile is a function of loader, indicating that the value of this field is obtained from Lobfile.
Terminated by EOF indicates that each LOB field of each line comes from a separate file.
