| Multiple INTO TABLE Statements [message #72619] |
Mon, 04 August 2003 21:25  |
Yeo Khian Wei
Messages: 1
Registered: August 2003
|
Junior Member |
|
|
Hi
Currently I'm trying to distinguish 2 different field columns so that my data can be inserted into the table accurately. However no matter how I tried, all the data could not be uploaded in. Here is my control file:
LOAD DATA
INFILE "c:upload.txt"
APPEND
INTO TABLE LOGADMIN.LOG_DATA3
WHEN USER_AGENT = "-"
FIELDS TERMINATED BY WHITESPACE
TRAILING NULLCOLS
(REMOTE_HOST POSITION (1),
USER_AGENT "LOWER(:USER_AGENT)",
DATE_ENTERED SYSDATE,
logid "LOGID3.nextval"
)
INTO TABLE LOGADMIN.LOG_DATA3
WHEN USER_AGENT <> "-"
FIELDS TERMINATED BY WHITESPACE
TRAILING NULLCOLS
(REMOTE_HOST POSITION (1),
USER_AGENT ENCLOSED BY '"' "LOWER(:USER_AGENT)",
DATE_ENTERED SYSDATE,
logid "LOGID3.nextval"
)
By doing this, I will be only able to insert data that contains "-" character in the user_agent field while the rest of the data that does not have the "-" character will be discarded. How do i re-write my control file so that I can allow all data to be uploaded? Thanks in advance.
|
|
|
|
| Re: Multiple INTO TABLE Statements [message #158833 is a reply to message #72619] |
Tue, 14 February 2006 11:26  |
rjsha1
Messages: 22
Registered: December 2005
Location: Birmingham England
|
Junior Member |
|
|
|
put POSITION(1) before the second set of SQL LOADER commands this makes SQL LOADER realise that it must go back to the start of the next logical line.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
