Shulou(Shulou.com)06/01 Report--

Specially reprint the article: http:/viewthread.php?tid=13979&extra=&page=1

A little summary of sql load

Sqlldr userid=lgone/tiger control=a.ctl

LOAD DATA

INFILE 't.dat' / / File to import

INFILE 'tt.date' / / Import multiple files

INFILE * / / the content to be imported is just below BEGINDATA in the control file followed by the imported content

INTO TABLE table_name / / specify the loaded table

BADFILE'c:\ bad.txt' / / specify a bad file address

* here are four ways to load tables

APPEND / / the data in the original table is added at the end.

INSERT / / load empty table if the original table has data sqlloader will stop the default value

REPLACE / / previous tables have data and all previous data will be deleted

TRUNCATE / / the same content as replace will delete existing data with the truncate statement

* the specified TERMINATED can be at the beginning of the table or in the internal fields section of the table

FIELDS TERMINATED BY', 'OPTIONALLY ENCLOSED BY' "'

/ / load this kind of data: 10 lg LG, "lg", "lg,lg"

/ / result in the table: 10 lg "lg" lg,lg

/ / TERMINATED BY X '09' / / in hexadecimal format' 09'

/ / TERMINATED BY WRITESPACE / / load this data: 10 lg lg

Null is allowed if the field of the TRAILING NULLCOLS * table has no corresponding value.

* the following are the fields of the table

(

Col_1, col_2, col_filler FILLER / / FILLER keywords the values of this column will not be loaded

/ / for example: lg,lg,not result lg lg

)

/ / when FIELDS TERMINATED BY','is not declared

/ / (

/ / col_1 [interger external] TERMINATED BY','

/ / col_2 [date "dd-mon-yyy"] TERMINATED BY','

/ / col_3 [char] TERMINATED BY', 'OPTIONALLY ENCLOSED BY' lg'

/ /)

/ / when FIELDS TERMINATED BY','is not declared, use the location to tell the field to load the data

/ / (

/ / col_1 position (1:2)

/ / col_2 position (3:10)

/ / col_3 position (*: 16), / / the start of this field is at the end of the previous field

/ / col_4 position (1:16)

/ / col_5 position (3:10) char (8) / / specify the type of field

/ /)

BEGINDATA / / corresponds to the initial INFILE * the content to be imported is in the control file

10,Sql,what

20,lg,show

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.