| Load data into table but delete some reocrds using sql*loader [message #355807] |
Mon, 27 October 2008 12:18  |
poratips
Messages: 345
Registered: April 2005
Location: IL
|
Senior Member |
|
|
Hi,
I am using sql*loader to load data everyday for some incoming file using Truncate option (reloading table) and also running.
Now when i trucate this table to load incoming file then my seperate loaded sql data (from my insert after sql*loader) also get truncated and i have to run again insert sql everyday.
My seperate sql'a pk id column starts with special char so i know that these are from my seperate isnert sqls.
Now how can i use the sql*loader so when i truncate/delete the table then i keep the seperate sql'a pk id column data which starts with special char and load only incoming file?
My table structure and sql*loader is:
CREATE TABLE CODE
(
ID VARCHAR2(40 BYTE) NOT NULL,
CODE_NAME VARCHAR2(5 BYTE) NOT NULL,
CODE_VALUE VARCHAR2(17 BYTE),
CTL VARCHAR2(6 BYTE),
DESC VARCHAR2(20 BYTE) NOT NULL,
JOB VARCHAR2(1 BYTE),
TERM VARCHAR2(1 BYTE),
CONSTRAINT CODE_PK PRIMARY KEY (ID)
SQL*LOADER:
---------
load data
infile DATA2.txt'
truncate
into table CODE
(id recnum,
code_name position(1:5),
code_value position(6:22),
ctl position(23:28),
desc position(29:48),
JOB position(49:49),
TERM position(50:50)
)
example Data:
1 EC01 B1 Beta J T
2 EC01 B2 Beta J T
3 EC01 B3 Beta J T
4 EC01 C1 test J T
5 EC01 C2 test J T
6 EC01 C3 test J T
7 EC01 C4 test J T
8 EC01 C5 test J T
9 EC01 C6 test J T
P1 EC01 PR1 MAX J T
P2 EC01 PR2 MAX J T
P3 EC03 PR3 MAX J T
Thanks,
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Load data into table but delete some reocrds using sql*loader [message #355832 is a reply to message #355826] |
Mon, 27 October 2008 17:01  |
poratips
Messages: 345
Registered: April 2005
Location: IL
|
Senior Member |
|
|
Thanks.
So i have to follow steps:
1)create the external table then load data into ext table
2)delete the data from original table using where id not like 'P%'
3) then run the Update/Insert script to load the new data into original table using external table
This is a daily job.
Please let me know if i am missing anything.
thanks,
|
|
|
|
|
|
|
|
| Re: Load data into table but delete some reocrds using sql*loader [message #363227 is a reply to message #361981] |
Fri, 05 December 2008 07:37 |
poratips
Messages: 345
Registered: April 2005
Location: IL
|
Senior Member |
|
|
Thanks.
Following is my current working control file.
load data
infile DATA2.txt'
truncate
into table CODE
(id recnum,
code_name position(1:5),
code_value position(6:22),
ctl position(23:28),
desc position(29:48),
JOB position(49:49),
TERM position(50:50)
)
And follwoing is the sample data look like from the file which i am loading everyday and also i am running insert script to load some data which is not in the file.
Now i need to keep this INSERT sql data as it is while i am loading through sql*loader but i am using Truncate option so its truncating whole table and i have to run each time this INSERT sql after sql*loader runs.If i add any Insert sql which is not part of the INSERT script and not into file then its also deleting when Sql*loader runs then i have to manually add insert sql in my original INSERT script which i am running after sql*loader runs but sometimes chances to forget to add into INSERT script and its get deleted then you don't know so i am looking some approach to handle this situation.
1 EC01 B1 Beta J T
2 EC01 B2 Beta J T
3 EC01 B3 Beta J T
4 EC01 C1 test J T
5 EC01 C2 test J T
6 EC01 C3 test J T
7 EC01 C4 test J T
8 EC01 C5 test J T
9 EC01 C6 test J T
P1 EC01 PR1 MAX J T
P2 EC01 PR2 MAX J T
P3 EC03 PR3 MAX J T
Thanks,
|
|
|
|
| Use partitions [message #379088 is a reply to message #355807] |
Sun, 04 January 2009 12:43 |
ignasi
Messages: 3
Registered: November 2008
|
Junior Member |
|
|
Hi there,
Keep the permanent data in one partition and temporary
on another partition.
...
into table <name> (partition <name>)
...
Regards
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
